DatabaseProcApplicationCreatedLinks
sybsystemprocssp_showtext  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** sp_showtext
4     **
5     **	Procedure to extract SQL text from syscomments and display it
6     **	with the actual line numbers that were present in the original
7     **	script used to create the compiled object. Supports options
8     **	via 'printopts' to produce line #s in comments, left/right-
9     **	justified, as a DDL script (i.e. with matching DROP command
10    **	generated) etc. See comments in sp_showtext_output for various
11    **	print options.
12    **
13    **	This procedure derives its main skeleton from sp_helptext but
14    **	then very soon diverges into SQL text parsing code that produces
15    **	properly formatted SQL text with line #s, and much more.
16    **
17    ** Parameters:
18    **	@objname	- Name of compiled object to generate text for.
19    **			  (Use ';n' to specify grouping # for sprocs.)
20    **	@startline	- Display text starting from this line #.
21    **	@numlines	- Number of lines to print, or context block to show 
22    **			  around SQL at line @startline
23    **	@printopts	- Various print options. See callee.
24    **	@trace		- Debugging trace flag. (Internal use only.)
25    {
26    */
27    create or replace procedure sp_showtext
28        @objname varchar(325) = NULL
29        , @startline int = 0
30        , @numlines_or_ctxt int = 0
31        , @printopts varchar(256) = NULL
32        , @trace int = 0
33    as
34        begin
35            declare @text_count int
36                , @sitename varchar(30) -- unused
37                , @dbname varchar(30)
38                , @ownername varchar(30)
39                , @obj_name varchar(260) -- allow for 5-byte  group #.
40                , @grouping_num int
41                , @group_num_str varchar(10)
42                , @config_parm int
43                , @issystemproc int
44                , @procval int
45                , @proc_id int
46                , @charindex int -- of ';' separator for groups
47                , @objtype varchar(2)
48                , @tot_members int
49                , @retval int
50                , @error int
51                , @ins_select_str varchar(1024)
52                , @nrows_inserted int
53                , @art_terminator char(1) -- artificial terminator byte
54    
55            /* Variables for granular permission permission checking. */
56            declare @nullarg char(1),
57                @dummy int,
58                @status int,
59                @gp_enabled int,
60                @privileged_user int
61    
62            select @privileged_user = 0
63    
64            /* If @objname is NULL, show some help info and quit. */
65            if (@objname IS NULL)
66            begin
67                exec sp_showtext_usage 5
68                    , 'showsql'
69                    , 'linenumbers'
70                    , 'comments'
71                    , 'leftjustify'
72                    , 'rightjustify'
73                    , 'ddlgen'
74                    , 'context'
75                    , 'noparams'
76                return 0
77            end
78    
79            if (@@trancount > 0)
80            begin
81                /* 17260, "Can't run %1! from within a transaction." */
82                raiserror 17260, "sp_showtext"
83                return (1)
84            end
85    
86            set chained off
87            set transaction isolation level 1
88            set nocount ON
89    
90            -- Crack open the name into its individual components.
91            exec sp_namecrack @objname, @sitename output
92                , @dbname output
93                , @ownername output
94                , @obj_name output
95    
96            /*
97            **  Make sure the @obj_name is local to the current database.
98            */
99            if (@dbname IS NOT NULL) and (@dbname != db_name())
100           begin
101               /* 17460, "Object must be in the current database." */
102               raiserror 17460
103               return (1)
104           end
105   
106           /* See if @obj_name has a group # reference. Strip it out first. */
107           select @charindex = charindex(';', @obj_name)
108           if (@charindex != 0)
109           begin
110               -- This is the piece of string following ';'
111               select @group_num_str = ltrim(rtrim(substring(@obj_name,
112                               (@charindex + 1),
113                               (char_length(@obj_name)
114                               - @charindex))))
115   
116               if (patindex("%[^0-9]%", @group_num_str) = 0)
117               begin
118                   select @grouping_num = convert(int, @group_num_str)
119                       , @obj_name = substring(@obj_name, 1, (@charindex - 1))
120               end
121               else
122               begin
123                   raiserror 19060, 'sp_showtext', @group_num_str
124                   return 1
125               end
126           end
127   
128           --  See if @objname exists. Reconstruct @objname from its parts.
129           select @objname = case when @dbname IS NULL then NULL else @dbname + '.' end
130               + case when (@ownername IS NULL and @dbname IS NULL)
131                   then NULL
132                   when @ownername IS NULL then "."
133                   else @ownername + '.'
134               end
135               + @obj_name
136   
137           if (object_id(@objname) is NULL)
138           begin
139               /* 17461, "Object does not exist in this database." */
140               raiserror 17461
141               return (1)
142           end
143   
144           /*
145           ** If the configuration parameter 'allow select on syscomments.text' is set to
146           ** 0, then  the user can use sp_showtext ONLY in the following cases
147           ** 
148           **	1. if the user has sa_role
149           **	2. if the object is a system stored procedure
150           **	3. if the object is owned by the user
151           **
152           */
153           select @config_parm = value
154           from master.dbo.syscurconfigs
155           where config = 258
156   
157           select @issystemproc = 1 /* It is a system stored procedure */
158   
159           if (@config_parm = 0)
160           begin
161   
162               /* 
163               ** If granular permissions is not enabled then sa_role is required.
164               ** If granular permissions is enabled then the permission 'own database'
165               ** is required.  proc_role and proc_auditperm will also do auditing
166               ** if required. Both will also print error message if required.
167               */
168   
169               select @nullarg = NULL
170               execute @status = sp_aux_checkroleperm "dbo", "own database",
171                   @dbname, @gp_enabled output
172   
173               if (@status = 0)
174               begin
175                   select @privileged_user = 1
176               end
177   
178   
179               /*
180               ** If the user is DBO or an account with sa_role, it's all fine
181               */
182               if (@privileged_user != 1)
183               begin
184                   if (db_name() != "master" and db_name() != "sybsystemprocs")
185                   begin
186                       /*
187                       ** System Stored Procedures can only be in master or
188                       ** sybsystemsprocs database.
189                       */
190                       select @issystemproc = 0
191                   end
192                   else
193                   begin
194                       /*
195                       ** We are in either sybsystemprocs or master database.
196                       ** See if the name starts with "sp_" and is owned by
197                       ** the DBO.
198                       */
199                       select @obj_name = name
200                       from sysobjects
201                       where id = object_id(@objname)
202                           and uid = 1
203                       if (substring(@obj_name, 1, 3) != "sp_")
204                           select @issystemproc = 0
205                   end
206   
207                   /*
208                   ** If it is not a system stored procedure, then it needs to be
209                   ** owned by the user.
210                   */
211                   if @issystemproc = 0
212                   begin
213                       /*
214                       ** The object needs to be owned by the user
215                       */
216                       if not exists (select name from sysobjects
217                               where uid = user_id()
218                                   and id = object_id(@objname))
219                       begin
220                           if (@gp_enabled = 0)
221                           begin
222                               raiserror 18180, "sa_role",
223                                   "sp_showtext"
224                           end
225                           else
226                           begin
227                               raiserror 18180,
228                                   "own database permission",
229                                   "sp_showtext"
230                           end
231                           return (1)
232                       end
233                   end
234               end
235               else
236               begin
237                   /*
238                   ** Audit this as a successful sa command execution if the user
239                   ** is a privileged user.
240                   */
241                   if (@gp_enabled = 0)
242                   begin
243                       select @dummy = proc_role("sa_role")
244                   end
245                   else
246                   begin
247                       select @dummy = proc_auditperm("own database",
248                               @status, @dbname)
249                   end
250               end
251           end
252   
253           /*  Validate grouping number */
254           select @objtype = rtrim(type) from sysobjects where id = object_id(@objname)
255   
256           /*
257           ** The group number is for stored procedures, but is overloaded
258           ** for the partition order of the partition condition object.
259           ** Using @grouping_num n means to get the nth partition condition
260           ** text.
261           */
262           if (@objtype NOT IN ('P', 'N')) and (@grouping_num is not null)
263           begin
264               -- You cannot use a grouping number for non-procedures.
265               raiserror 18936
266               return (1)
267           end
268   
269           /*
270           ** Find out how many lines of text are coming back.
271           ** and return if there are none.
272           */
273           select @text_count = count(*)
274           from syscomments
275           where id = object_id(@objname)
276               and ((@grouping_num IS NULL) OR (number = @grouping_num))
277   
278           if (@text_count = 0)
279           begin
280               if (@grouping_num IS NULL)
281               begin
282                   /* 17679, "There is no text for object @objname." */
283                   raiserror 17679, @objname
284               end
285               else
286               begin
287                   raiserror 18937, @objname, @grouping_num
288               end
289               return 1
290           end
291   
292           -- Create a template temp table for use in all cases.
293           -- The charlength_text column is used to first save off the length of the text
294           -- column that will be fetched. That length is needed if in case the text
295           -- column has any trailing blanks that get truncated, we need to re-gen those
296           -- missing blanks during printing. Use 'master.dbo.syscomments' to avoid any
297           -- unnecessary catalog b/locking issues in tempdb.
298           --
299           select line = identity (5), number
300               , text = convert(varchar(257), "")
301               , charlength_text = convert(int, 0)
302               , colid2, colid
303           into #syscomments
304           from master.dbo.syscomments
305           where 1 = 0
306   
307           -- Start generating the INSERT SELECT SQL statement that will then be
308           -- changed based on whether the user has requested for a sproc with a
309           -- particular group #.
310           --
311           select @art_terminator = '`'
312           select @ins_select_str = "INSERT #syscomments(number, text, charlength_text"
313               + ", colid2, colid)"
314               + " SELECT number"
315               + ", text + '" + @art_terminator + "'"
316               + ", char_length(text + '" + @art_terminator + "')"
317               + ", colid2, colid"
318               + " FROM syscomments"
319               + " WHERE id = object_id('" + @objname + "')"
320   
321           /*
322           NOTE: If you add an ORDER BY to do some improved INSERT SELECT,
323           we seem to be running in an issue (at least in 15.0) where the
324           'text' column is being truncated before the ORDER BY is applied.
325           So this messes up the result lengths etc., and we end up losing
326           trailing blanks if the 'text' column had some trailing blanks.
327   
328           order by number, colid2, colid
329           print "Data from SELECT ..."
330           select number
331           , charlength_text = char_length(text + ']') -- - 1
332           , colid2, colid
333           from syscomments
334           where id = object_id(@objname)
335           and colid IN (9, 13, 21, 26, 4245, 46)
336   
337           print "Data from INSERTED table #syscomments..."
338           select number, charlength_text, colid2, colid
339           from #syscomments
340           where colid IN (9, 13, 21, 26, 4245, 46)
341           */
342   
343           /* procs with @grouping_num >= 0 */
344           if (@grouping_num is NOT NULL)
345           begin
346               select @ins_select_str = @ins_select_str
347                   + " AND number = "
348                   + str(@grouping_num)
349           end
350   
351           -- Ensure that we select rows to be inserted in the proper order, so that the
352           -- text can be re-generated in the order it was created. In all possible cases,
353           -- the correct order of rows is achieved  by the key (number, texttype, colid2, 
354           -- colid). 
355           select @ins_select_str = @ins_select_str
356               + " ORDER BY texttype, colid2, colid"
357   
358           -- Execute the INSERT SELECT, and bail out if there are errors.
359           exec (@ins_select_str)
360           select @error = @@error, @nrows_inserted = @@rowcount
361           if (@error != 0) or (@nrows_inserted != @text_count)
362           begin
363               print "Internal error. Inserted %1! rows to temporary table '%2!'. Expected to insert %3! rows.",
364                   @nrows_inserted, "#syscomments", @text_count
365               return @error
366           end
367   
368           -- Hopefully a faster access method with ORDER BY line clause.
369           create index syscomm_ncind_line on #syscomments(line)
370   
371           -- Handle NULL args for those that we really want defaults.
372           --
373           if (@startline IS NULL) select @startline = 0
374           if (@numlines_or_ctxt IS NULL) select @numlines_or_ctxt = 0
375   
376           exec @retval = sp_showtext_output @objname, @objtype
377               , @startline
378               , @numlines_or_ctxt
379               , @printopts
380               , @trace
381               , @art_terminator
382           if (@retval != 0)
383               return (@retval)
384       end -- }
385   


exec sp_procxmode 'sp_showtext', 'AnyMode'
go

Grant Execute on sp_showtext to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 300
 MTYP 4 Assignment type mismatch @object: varchar(255) = varchar(260) 94
 MTYP 4 Assignment type mismatch @objname: varchar(317) = varchar(325) 376
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 276
 QTYP 4 Comparison type mismatch smallint = int 276
 TNOI 4 Table with no index master..syscurconfigs master..syscurconfigs
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 201
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 218
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 254
 MAW1 3 Warning message on %name% sybsystemprocs..syscomments.id: Warning message on syscomments 275
 MGTP 3 Grant to public master..syscomments  
 MGTP 3 Grant to public master..syscurconfigs  
 MGTP 3 Grant to public sybsystemprocs..sp_showtext  
 MGTP 3 Grant to public sybsystemprocs..syscomments  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 67
 MNER 3 No Error Check should check return value of exec 91
 MNER 3 No Error Check should check @@error after select into 299
 MUCO 3 Useless Code Useless Begin-End Pair 34
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 79
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 137
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 173
 MUCO 3 Useless Code Useless Brackets 182
 MUCO 3 Useless Code Useless Brackets 184
 MUCO 3 Useless Code Useless Brackets 203
 MUCO 3 Useless Code Useless Brackets 220
 MUCO 3 Useless Code Useless Brackets 231
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 266
 MUCO 3 Useless Code Useless Brackets 278
 MUCO 3 Useless Code Useless Brackets 280
 MUCO 3 Useless Code Useless Brackets 344
 MUCO 3 Useless Code Useless Brackets 373
 MUCO 3 Useless Code Useless Brackets 374
 MUCO 3 Useless Code Useless Brackets 382
 MUCO 3 Useless Code Useless Brackets 383
 QAFM 3 Var Assignment from potentially many rows 153
 QISO 3 Set isolation level 87
 QPNC 3 No column in condition 276
 QPNC 3 No column in condition 305
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscomments.csyscomments unique clustered
(id, number, colid2, colid, texttype)
Intersection: {number, id}
275
 VNRD 3 Variable is not read @sitename 91
 VNRD 3 Variable is not read @nullarg 169
 VNRD 3 Variable is not read @dummy 247
 VUNU 3 Variable is not used @procval 44
 VUNU 3 Variable is not used @proc_id 45
 VUNU 3 Variable is not used @tot_members 48
 MDYS 2 Dynamic SQL Marker 359
 MSUB 2 Subquery Marker 216
 MTR1 2 Metrics: Comments Ratio Comments: 47% 27
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 25 = 33dec - 10exi + 2 27
 MTR3 2 Metrics: Query Complexity Complexity: 127 27

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscomments  
reads table master..syscurconfigs (1)  
reads table sybsystemprocs..sysobjects  
reads table master..syscomments (1)  
calls proc sybsystemprocs..sp_showtext_usage  
   calls proc sybsystemprocs..sp_getmessage  
      reads table sybsystemprocs..sysusermessages  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table master..syslanguages (1)  
      reads table master..sysmessages (1)  
writes table tempdb..#syscomments (1) 
calls proc sybsystemprocs..sp_namecrack  
calls proc sybsystemprocs..sp_showtext_output  
   calls proc sybsystemprocs..sp_showtext_usage  
   calls proc sybsystemprocs..sp_showtext_print_line  
   calls proc sybsystemprocs..sp_getopts  
   calls proc sybsystemprocs..sp_showtext_sproc_tagline  
      calls proc sybsystemprocs..sp_help_params  
         calls proc sybsystemprocs..sp_autoformat  
            calls proc sybsystemprocs..sp_namecrack  
            writes table sybsystemprocs..sp_autoformat_rset_002 
            calls proc sybsystemprocs..sp_autoformat  
            writes table sybsystemprocs..sp_autoformat_rset_001 
            reads table tempdb..systypes (1)  
            writes table sybsystemprocs..sp_autoformat_rset_005 
            read_writes table tempdb..#colinfo_af (1) 
            writes table sybsystemprocs..sp_autoformat_rset_003 
            reads table master..systypes (1)  
            writes table sybsystemprocs..sp_autoformat_rset_004 
            reads table master..syscolumns (1)  
            reads table tempdb..syscolumns (1)  
         reads table sybsystemprocs..syscolumns  
         reads table sybsystemprocs..systypes  
         reads table master..spt_values (1)  
         reads table master..sysmessages (1)  
         reads table sybsystemprocs..sysxtypes  
         read_writes table tempdb..#helpproc (1) 
   calls proc sybsystemprocs..sp_showtext_check_print  
   reads table tempdb..#syscomments (1) 
   reads table sybsystemprocs..syscomments  
   calls proc sybsystemprocs..sp_getmessage  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  

CALLERS
called by proc sybsystemprocs..sp_helptext_usage  
   called by proc sybsystemprocs..sp_helptext  
called by proc sybsystemprocs..sp_helptext