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 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 /* If @objname is NULL, show some help info and quit. */
56 if (@objname IS NULL)
57 begin
58 exec sp_showtext_usage 5
59 , 'showsql'
60 , 'linenumbers'
61 , 'comments'
62 , 'leftjustify'
63 , 'rightjustify'
64 , 'ddlgen'
65 , 'context'
66 , 'noparams'
67 return 0
68 end
69
70 if (@@trancount > 0)
71 begin
72 /* 17260, "Can't run %1! from within a transaction." */
73 raiserror 17260, "sp_showtext"
74 return (1)
75 end
76
77 set chained off
78 set transaction isolation level 1
79 set nocount ON
80
81 -- Crack open the name into its individual components.
82 exec sp_namecrack @objname, @sitename output
83 , @dbname output
84 , @ownername output
85 , @obj_name output
86
87 /*
88 ** Make sure the @obj_name is local to the current database.
89 */
90 if (@dbname IS NOT NULL) and (@dbname != db_name())
91 begin
92 /* 17460, "Object must be in the current database." */
93 raiserror 17460
94 return (1)
95 end
96
97 /* See if @obj_name has a group # reference. Strip it out first. */
98 select @charindex = charindex(';', @obj_name)
99 if (@charindex != 0)
100 begin
101 -- This is the piece of string following ';'
102 select @group_num_str = ltrim(rtrim(substring(@obj_name,
103 (@charindex + 1),
104 (char_length(@obj_name)
105 - @charindex))))
106
107 if (patindex("%[^0-9]%", @group_num_str) = 0)
108 begin
109 select @grouping_num = convert(int, @group_num_str)
110 , @obj_name = substring(@obj_name, 1, (@charindex - 1))
111 end
112 else
113 begin
114 raiserror 19060, 'sp_showtext', @group_num_str
115 return 1
116 end
117 end
118
119 -- See if @objname exists. Reconstruct @objname from its parts.
120 select @objname = case when @dbname IS NULL then NULL else @dbname + '.' end
121 + case when (@ownername IS NULL and @dbname IS NULL)
122 then NULL
123 when @ownername IS NULL then "."
124 else @ownername + '.'
125 end
126 + @obj_name
127
128 if (object_id(@objname) is NULL)
129 begin
130 /* 17461, "Object does not exist in this database." */
131 raiserror 17461
132 return (1)
133 end
134
135 /*
136 ** If the configuration parameter 'allow select on syscomments.text' is set to
137 ** 0, then the user can use sp_showtext ONLY in the following cases
138 **
139 ** 1. if the user has sa_role
140 ** 2. if the object is a system stored procedure
141 ** 3. if the object is owned by the user
142 **
143 */
144 select @config_parm = value
145 from master.dbo.syscurconfigs
146 where config = 258
147
148 select @issystemproc = 1 /* It is a system stored procedure */
149
150 if (@config_parm = 0)
151 begin
152 /*
153 ** If the user is DBO or an account with sa_role, it's all fine
154 */
155 if (user_id() != 1)
156 begin
157 if (db_name() != "master" and db_name() != "sybsystemprocs")
158 begin
159 /*
160 ** System Stored Procedures can only be in master or
161 ** sybsystemsprocs database.
162 */
163 select @issystemproc = 0
164 end
165 else
166 begin
167 /*
168 ** We are in either sybsystemprocs or master database.
169 ** See if the name starts with "sp_" and is owned by
170 ** the DBO.
171 */
172 select @obj_name = name
173 from sysobjects
174 where id = object_id(@objname)
175 and uid = 1
176 if (substring(@obj_name, 1, 3) != "sp_")
177 select @issystemproc = 0
178 end
179
180 /*
181 ** If it is not a system stored procedure, then it needs to be
182 ** owned by the user.
183 */
184 if @issystemproc = 0
185 begin
186 /*
187 ** The object needs to be owned by the user
188 */
189 if not exists (select name from sysobjects
190 where uid = user_id()
191 and id = object_id(@objname))
192 begin
193 raiserror 18180
194 return (1)
195 end
196 end
197 end
198 else
199 begin
200 /*
201 ** Audit this as a successful sa command execution if the user
202 ** has sa role.
203 */
204 if charindex("sa_role", show_role()) > 0
205 select @procval = proc_role("sa_role")
206 end
207 end
208
209 /* Validate grouping number */
210 select @objtype = rtrim(type) from sysobjects where id = object_id(@objname)
211
212 /*
213 ** The group number is for stored procedures, but is overloaded
214 ** for the partition order of the partition condition object.
215 ** Using @grouping_num n means to get the nth partition condition
216 ** text.
217 */
218 if (@objtype NOT IN ('P', 'N')) and (@grouping_num is not null)
219 begin
220 -- You cannot use a grouping number for non-procedures.
221 raiserror 18936
222 return (1)
223 end
224
225 /*
226 ** Find out how many lines of text are coming back.
227 ** and return if there are none.
228 */
229 select @text_count = count(*)
230 from syscomments
231 where id = object_id(@objname)
232 and ((@grouping_num IS NULL) OR (number = @grouping_num))
233
234 if (@text_count = 0)
235 begin
236 if (@grouping_num IS NULL)
237 begin
238 /* 17679, "There is no text for object @objname." */
239 raiserror 17679, @objname
240 end
241 else
242 begin
243 raiserror 18937, @objname, @grouping_num
244 end
245 return 1
246 end
247
248 -- Create a template temp table for use in all cases.
249 -- The charlength_text column is used to first save off the length of the text
250 -- column that will be fetched. That length is needed if in case the text
251 -- column has any trailing blanks that get truncated, we need to re-gen those
252 -- missing blanks during printing. Use 'master.dbo.syscomments' to avoid any
253 -- unnecessary catalog b/locking issues in tempdb.
254 --
255 select line = identity (5), number
256 , text = convert(varchar(257), "")
257 , charlength_text = convert(int, 0)
258 , colid2, colid
259 into #syscomments
260 from master.dbo.syscomments
261 where 1 = 0
262
263 -- Start generating the INSERT SELECT SQL statement that will then be
264 -- changed based on whether the user has requested for a sproc with a
265 -- particular group #.
266 --
267 select @art_terminator = '`'
268 select @ins_select_str = "INSERT #syscomments(number, text, charlength_text"
269 + ", colid2, colid)"
270 + " SELECT number"
271 + ", text + '" + @art_terminator + "'"
272 + ", char_length(text + '" + @art_terminator + "')"
273 + ", colid2, colid"
274 + " FROM syscomments"
275 + " WHERE id = object_id('" + @objname + "')"
276
277 /*
278 NOTE: If you add an ORDER BY to do some improved INSERT SELECT,
279 we seem to be running in an issue (at least in 15.0) where the
280 'text' column is being truncated before the ORDER BY is applied.
281 So this messes up the result lengths etc., and we end up losing
282 trailing blanks if the 'text' column had some trailing blanks.
283
284 order by number, colid2, colid
285 print "Data from SELECT ..."
286 select number
287 , charlength_text = char_length(text + ']') -- - 1
288 , colid2, colid
289 from syscomments
290 where id = object_id(@objname)
291 and colid IN (9, 13, 21, 26, 4245, 46)
292
293 print "Data from INSERTED table #syscomments..."
294 select number, charlength_text, colid2, colid
295 from #syscomments
296 where colid IN (9, 13, 21, 26, 4245, 46)
297 */
298
299 /* procs with @grouping_num >= 0 */
300 if (@grouping_num is NOT NULL)
301 begin
302 select @ins_select_str = @ins_select_str
303 + " AND number = "
304 + str(@grouping_num)
305 end
306
307 -- Ensure that we select rows to be inserted in the proper order, so that the
308 -- text can be re-generated in the order it was created. In all possible cases,
309 -- the correct order of rows is achieved by the key (number, texttype, colid2,
310 -- colid).
311 select @ins_select_str = @ins_select_str
312 + " ORDER BY texttype, colid2, colid"
313
314 -- Execute the INSERT SELECT, and bail out if there are errors.
315 exec (@ins_select_str)
316 select @error = @@error, @nrows_inserted = @@rowcount
317 if (@error != 0) or (@nrows_inserted != @text_count)
318 begin
319 print "Internal error. Inserted %1! rows to temporary table '%2!'. Expected to insert %3! rows.",
320 @nrows_inserted, "#syscomments", @text_count
321 return @error
322 end
323
324 -- Hopefully a faster access method with ORDER BY line clause.
325 create index syscomm_ncind_line on #syscomments(line)
326
327 -- Handle NULL args for those that we really want defaults.
328 --
329 if (@startline IS NULL) select @startline = 0
330 if (@numlines_or_ctxt IS NULL) select @numlines_or_ctxt = 0
331
332 exec @retval = sp_showtext_output @objname, @objtype
333 , @startline
334 , @numlines_or_ctxt
335 , @printopts
336 , @trace
337 , @art_terminator
338 if (@retval != 0)
339 return (@retval)
340 end -- }
341
exec sp_procxmode 'sp_showtext', 'AnyMode'
go
Grant Execute on sp_showtext to public
go
