DatabaseProcApplicationCreatedLinks
sybsystemprocssp_options  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /* 
4     ** Messages for "sp_options".
5     **
6     ** 19194, "Argument '%1!' is either invalid or non-unique. Valid arguments are: %2!"
7     ** 19613, "The session id %1! does not exist."
8     ** 19614, "The combination of input parameters %1! is invalid. Check user documentation and reenter command."
9     ** 19615, "No option or category matching '%1!' is found. Valid categories are:"
10    **
11    */
12    
13    create or replace procedure sp_options
14        @actionitem varchar(255) = NULL,
15        @name varchar(255) = NULL,
16        @displaytype varchar(255) = NULL,
17        @sessionid int = NULL
18    as
19    
20        begin
21            declare @currentcategory varchar(100)
22            declare @msg varchar(255)
23            declare @iscategory tinyint
24            declare @isoption tinyint
25            declare @islegalspid tinyint
26            declare @optioncount smallint
27    
28            /*
29            ** Removing additional spaces and setting input strings
30            ** to lower case strings.
31            */
32            select @actionitem = lower(ltrim(rtrim(@actionitem)))
33            select @displaytype = lower(ltrim(rtrim(@displaytype)))
34    
35            /*
36            ** Display syntax of this stored procedure.
37            */
38            if (@actionitem is NULL or @actionitem = "help")
39            begin
40                print ""
41                print "sp_options [[show|help"
42                print "             [, <option_name>|<category_name>|null"
43                print "              [, dflt|non_dflt|null"
44                print "               [, <spid>]"
45                print "              ]"
46                print "             ]"
47                print "           ]]"
48                return (0)
49            end
50    
51            /*
52            ** 19194, "Argument '%1!' is either invalid or non-unique.
53            ** Valid arguments are: %2!"
54            */
55            if (@actionitem != "help" and @actionitem != "show")
56            begin
57                select @msg = "'help' and 'show'"
58                raiserror 19194, @actionitem, @msg
59                return (1)
60            end
61    
62            /*
63            ** If session id is not provided, use current session's id.
64            */
65            if (@sessionid is NULL)
66            begin
67                select @sessionid = @@spid
68            end
69    
70            /*
71            ** cache sysoptions data for given session id.
72            */
73            begin
74                /* Adaptive Server has expanded all '*' elements in the following statement */ select [master].dbo.sysoptions.spid, [master].dbo.sysoptions.name, [master].dbo.sysoptions.category, [master].dbo.sysoptions.currentsetting, [master].dbo.sysoptions.defaultsetting, [master].dbo.sysoptions.scope, [master].dbo.sysoptions.number
75                into #cachetable
76                from master.dbo.sysoptions
77                where spid = @sessionid
78            end
79    
80            /*
81            ** Check if given session-id is valid.
82            */
83            begin
84                select @islegalspid = count(*)
85                from #cachetable
86    
87                if (@islegalspid = 0)
88                begin
89                    /*
90                    ** 19613, "The spid %d does not exist."
91                    */
92                    raiserror 19613, @sessionid
93                    return (1)
94                end
95            end
96    
97            /*
98            ** If action item is set to 'show' and no name is
99            ** provided, then display options of given session
100           ** grouped by their categories.
101           */
102           if (@actionitem = "show" and @name is NULL)
103           begin
104               declare category_cur cursor for
105               select distinct category
106               from #cachetable
107   
108               open category_cur
109   
110               /*
111               ** Display all options.
112               */
113               if (@displaytype is NULL)
114               begin
115                   fetch category_cur into @currentcategory
116                   while (@@sqlstatus = 0)
117                   begin
118                       select name, currentsetting, defaultsetting, scope
119                       into #temptable
120                       from #cachetable
121                       where category = @currentcategory
122   
123                       select @msg = "Category: " + @currentcategory
124                       print ""
125                       print @msg
126                       print ""
127                       exec sp_autoformat #temptable
128                       drop table #temptable
129                       fetch category_cur into @currentcategory
130                   end
131                   close category_cur
132                   return (0)
133               end
134   
135               /*
136               ** Display default values of all options.
137               */
138               if (@displaytype = "dflt")
139               begin
140                   fetch category_cur into @currentcategory
141                   while (@@sqlstatus = 0)
142                   begin
143                       select name, defaultsetting
144                       into #temptable1
145                       from #cachetable
146                       where category = @currentcategory
147   
148                       select @msg = "Category: " + @currentcategory
149                       print ""
150                       print @msg
151                       print ""
152                       exec sp_autoformat #temptable1
153                       drop table #temptable1
154                       fetch category_cur into @currentcategory
155                   end
156                   close category_cur
157                   return (0)
158               end
159   
160               /*
161               ** Display all options, which are not set to their
162               ** default values.
163               */
164               if (@displaytype = "non_dflt")
165               begin
166                   fetch category_cur into @currentcategory
167                   while (@@sqlstatus = 0)
168                   begin
169                       select name, currentsetting, defaultsetting
170                       into #temptable2
171                       from #cachetable
172                       where
173                           category = @currentcategory
174                           and currentsetting != defaultsetting
175   
176                       select @msg = "Category: " + @currentcategory
177                       print ""
178                       print @msg
179                       print ""
180                       exec sp_autoformat #temptable2
181                       drop table #temptable2
182                       fetch category_cur into @currentcategory
183                   end
184                   close category_cur
185                   return (0)
186               end
187   
188               /*
189               ** 19194, "Argument '%1!' is either invalid or non-unique.
190               ** Valid arguments are: %2!"
191               */
192               select @msg = "'NULL', 'dflt' and 'non_dflt'"
193               raiserror 19194, @displaytype, @msg
194               close category_cur
195               return (1)
196           end
197   
198           /*
199           ** If action item is set to 'show' and a name is also
200           ** provided. Display information regarding given name.
201           */
202           if (@actionitem = "show" and @name is not NULL)
203           begin
204   
205               /*
206               ** Check if given name is a category.
207               */
208               select @iscategory = count(*)
209               from #cachetable
210               where category = @name
211   
212               /*
213               ** If given name is a category, display information
214               ** for given category only.
215               */
216               if (@iscategory > 0)
217               begin
218   
219                   /*
220                   ** Display all options in given category.
221                   */
222                   if (@displaytype is NULL)
223                   begin
224                       select name, currentsetting, defaultsetting, scope
225                       into #temptable3
226                       from #cachetable
227                       where category = @name
228   
229                       select @msg = "Category: " + @name
230                       print ""
231                       print @msg
232                       print ""
233                       exec sp_autoformat #temptable3
234                       return (0)
235                   end
236   
237                   /*
238                   ** Display default values of all options in given
239                   ** category.
240                   */
241                   if (@displaytype = "dflt")
242                   begin
243                       select name, defaultsetting
244                       into #temptable4
245                       from #cachetable
246                       where category = @name
247   
248                       select @msg = "Category: " + @name
249                       print ""
250                       print @msg
251                       print ""
252                       exec sp_autoformat #temptable4
253                       return (0)
254                   end
255   
256                   /*
257                   ** Display all options in given category, which are
258                   ** not set to their default values.
259                   */
260                   if (@displaytype = "non_dflt")
261                   begin
262                       select name, currentsetting, defaultsetting
263                       into #temptable5
264                       from #cachetable
265                       where category = @name and currentsetting != defaultsetting
266   
267                       select @msg = "Category: " + @name
268                       print ""
269                       print @msg
270                       print ""
271                       exec sp_autoformat #temptable5
272                       return (0)
273                   end
274   
275                   /*
276                   ** 19194, "Argument '%1!' is either invalid or non-unique.
277                   ** Valid arguments are: %2!"
278                   */
279                   select @msg = "'NULL', 'dflt' and 'non_dflt'"
280                   raiserror 19194, @displaytype, @msg
281                   return (1)
282               end
283   
284               /*
285               ** Check if given name is an option.
286               */
287               select @isoption = count(*)
288               from #cachetable
289               where name = @name
290   
291               if (@isoption = 1)
292               begin
293                   /*
294                   ** Display all information for given option.
295                   */
296                   if (@displaytype is NULL)
297                   begin
298                       select name, category, currentsetting, defaultsetting, scope
299                       into #temptable6
300                       from #cachetable
301                       where name = @name
302   
303                       exec sp_autoformat #temptable6
304                       return (0)
305                   end
306   
307                   /*
308                   ** Display default value for given option.
309                   */
310                   if (@displaytype = "dflt")
311                   begin
312                       select name, defaultsetting
313                       into #temptable7
314                       from #cachetable
315                       where name = @name
316   
317                       exec sp_autoformat #temptable7
318                       return (0)
319                   end
320   
321                   /*
322                   ** 19614, "The combination of input parameters %s 
323                   ** is invalid. Check user documentation and reenter 
324                   ** command."
325                   */
326                   if (@displaytype = "non_dflt")
327                   begin
328                       select @msg = @name + " and non_dflt"
329                       raiserror 19614, @msg
330                       return (1)
331                   end
332   
333                   /*
334                   ** 19194, "Argument '%1!' is either invalid or non-unique.
335                   ** Valid arguments are: %2!"
336                   */
337                   select @msg = "'NULL', 'dflt' and 'non_dflt'"
338                   raiserror 19194, @displaytype, @msg
339                   return (1)
340               end
341   
342               /*
343               ** Check if there are any options matching given name.
344               */
345               select @optioncount = count(*)
346               from #cachetable
347               where name like "%" + @name + "%"
348   
349               /*
350               ** Display information of all matching options, grouped
351               ** by category.
352               */
353               if (@optioncount > 0)
354               begin
355                   declare category_cur cursor for
356                   select distinct category
357                   from #cachetable
358   
359                   open category_cur
360   
361                   /*
362                   ** Display all matching options.
363                   */
364                   if (@displaytype is NULL)
365                   begin
366                       fetch category_cur into @currentcategory
367                       while (@@sqlstatus = 0)
368                       begin
369                           select name, currentsetting, defaultsetting, scope
370                           into #temptable8
371                           from #cachetable
372                           where name like "%" + @name + "%"
373                               and category = @currentcategory
374   
375                           if exists (select name from #temptable8)
376                           begin
377                               select @msg = "Category: " + @currentcategory
378                               print ""
379                               print @msg
380                               print ""
381                               exec sp_autoformat #temptable8
382                           end
383                           drop table #temptable8
384                           fetch category_cur into @currentcategory
385                       end
386                       close category_cur
387                       return (0)
388                   end
389   
390                   /*
391                   ** Display default values of all matching options.
392                   */
393                   if (@displaytype = "dflt")
394                   begin
395                       fetch category_cur into @currentcategory
396                       while (@@sqlstatus = 0)
397                       begin
398                           select name, defaultsetting
399                           into #temptable9
400                           from #cachetable
401                           where name like "%" + @name + "%"
402                               and category = @currentcategory
403   
404                           if exists (select name from #temptable9)
405                           begin
406                               select @msg = "Category: " + @currentcategory
407                               print ""
408                               print @msg
409                               print ""
410                               exec sp_autoformat #temptable9
411                           end
412                           drop table #temptable9
413                           fetch category_cur into @currentcategory
414                       end
415                       close category_cur
416                       return (0)
417                   end
418   
419                   /*
420                   ** Display all matching options which are not set to
421                   ** their default values.
422                   */
423                   if (@displaytype = "non_dflt")
424                   begin
425                       fetch category_cur into @currentcategory
426                       while (@@sqlstatus = 0)
427                       begin
428                           select name, currentsetting, defaultsetting
429                           into #temptable10
430                           from #cachetable
431                           where name like "%" + @name + "%"
432                               and currentsetting != defaultsetting
433                               and category = @currentcategory
434   
435                           if exists (select name from #temptable10)
436                           begin
437                               select @msg = "Category: " + @currentcategory
438                               print ""
439                               print @msg
440                               print ""
441                               exec sp_autoformat #temptable10
442                           end
443                           drop table #temptable10
444                           fetch category_cur into @currentcategory
445                       end
446                       close category_cur
447                       return (0)
448                   end
449   
450                   /*
451                   ** 19194, "Argument '%1!' is either invalid or non-unique.
452                   ** Valid arguments are: %2!"
453                   */
454                   select @msg = "'NULL', 'dflt' and 'non_dflt'"
455                   raiserror 19194, @displaytype, @msg
456                   close category_cur
457                   return (1)
458               end
459   
460               /*
461               ** 19615, "No option or category matching '%1!' is found. Valid categories are:"
462               */
463               if (@optioncount = 0)
464               begin
465                   raiserror 19615, @name
466   
467                   select distinct category
468                   into #temptable11
469                   from #cachetable
470                   exec sp_autoformat #temptable11
471                   return (1)
472               end
473           end
474       end
475   


exec sp_procxmode 'sp_options', 'AnyMode'
go

Grant Execute on sp_options to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 40
 MEST 4 Empty String will be replaced by Single Space 124
 MEST 4 Empty String will be replaced by Single Space 126
 MEST 4 Empty String will be replaced by Single Space 149
 MEST 4 Empty String will be replaced by Single Space 151
 MEST 4 Empty String will be replaced by Single Space 177
 MEST 4 Empty String will be replaced by Single Space 179
 MEST 4 Empty String will be replaced by Single Space 230
 MEST 4 Empty String will be replaced by Single Space 232
 MEST 4 Empty String will be replaced by Single Space 249
 MEST 4 Empty String will be replaced by Single Space 251
 MEST 4 Empty String will be replaced by Single Space 268
 MEST 4 Empty String will be replaced by Single Space 270
 MEST 4 Empty String will be replaced by Single Space 378
 MEST 4 Empty String will be replaced by Single Space 380
 MEST 4 Empty String will be replaced by Single Space 407
 MEST 4 Empty String will be replaced by Single Space 409
 MEST 4 Empty String will be replaced by Single Space 438
 MEST 4 Empty String will be replaced by Single Space 440
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 127
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 152
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 180
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 233
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 252
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 271
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 303
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 317
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 381
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 410
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 441
 MUSP 4 Unquoted String Parameter sybsystemprocs..sp_autoformat: parameter # 1 470
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 77
 TNOI 4 Table with no index master..sysoptions master..sysoptions
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause category_cur 105
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause category_cur 356
 MGTP 3 Grant to public master..sysoptions  
 MGTP 3 Grant to public sybsystemprocs..sp_options  
 MNEJ 3 'Not Equal' join 174
 MNEJ 3 'Not Equal' join 265
 MNEJ 3 'Not Equal' join 432
 MNER 3 No Error Check should check @@error after select into 74
 MNER 3 No Error Check should check @@error after select into 118
 MNER 3 No Error Check should check return value of exec 127
 MNER 3 No Error Check should check @@error after select into 143
 MNER 3 No Error Check should check return value of exec 152
 MNER 3 No Error Check should check @@error after select into 169
 MNER 3 No Error Check should check return value of exec 180
 MNER 3 No Error Check should check @@error after select into 224
 MNER 3 No Error Check should check return value of exec 233
 MNER 3 No Error Check should check @@error after select into 243
 MNER 3 No Error Check should check return value of exec 252
 MNER 3 No Error Check should check @@error after select into 262
 MNER 3 No Error Check should check return value of exec 271
 MNER 3 No Error Check should check @@error after select into 298
 MNER 3 No Error Check should check return value of exec 303
 MNER 3 No Error Check should check @@error after select into 312
 MNER 3 No Error Check should check return value of exec 317
 MNER 3 No Error Check should check @@error after select into 369
 MNER 3 No Error Check should check return value of exec 381
 MNER 3 No Error Check should check @@error after select into 398
 MNER 3 No Error Check should check return value of exec 410
 MNER 3 No Error Check should check @@error after select into 428
 MNER 3 No Error Check should check return value of exec 441
 MNER 3 No Error Check should check @@error after select into 467
 MNER 3 No Error Check should check return value of exec 470
 MUCO 3 Useless Code Useless Begin-End Pair 20
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 48
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Begin-End Pair 73
 MUCO 3 Useless Code Useless Begin-End Pair 83
 MUCO 3 Useless Code Useless Brackets 87
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 138
 MUCO 3 Useless Code Useless Brackets 141
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 164
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 185
 MUCO 3 Useless Code Useless Brackets 195
 MUCO 3 Useless Code Useless Brackets 202
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 222
 MUCO 3 Useless Code Useless Brackets 234
 MUCO 3 Useless Code Useless Brackets 241
 MUCO 3 Useless Code Useless Brackets 253
 MUCO 3 Useless Code Useless Brackets 260
 MUCO 3 Useless Code Useless Brackets 272
 MUCO 3 Useless Code Useless Brackets 281
 MUCO 3 Useless Code Useless Brackets 291
 MUCO 3 Useless Code Useless Brackets 296
 MUCO 3 Useless Code Useless Brackets 304
 MUCO 3 Useless Code Useless Brackets 310
 MUCO 3 Useless Code Useless Brackets 318
 MUCO 3 Useless Code Useless Brackets 326
 MUCO 3 Useless Code Useless Brackets 330
 MUCO 3 Useless Code Useless Brackets 339
 MUCO 3 Useless Code Useless Brackets 353
 MUCO 3 Useless Code Useless Brackets 364
 MUCO 3 Useless Code Useless Brackets 367
 MUCO 3 Useless Code Useless Brackets 387
 MUCO 3 Useless Code Useless Brackets 393
 MUCO 3 Useless Code Useless Brackets 396
 MUCO 3 Useless Code Useless Brackets 416
 MUCO 3 Useless Code Useless Brackets 423
 MUCO 3 Useless Code Useless Brackets 426
 MUCO 3 Useless Code Useless Brackets 447
 MUCO 3 Useless Code Useless Brackets 457
 MUCO 3 Useless Code Useless Brackets 463
 MUCO 3 Useless Code Useless Brackets 471
 QCTC 3 Conditional Table Creation 118
 QCTC 3 Conditional Table Creation 143
 QCTC 3 Conditional Table Creation 169
 QCTC 3 Conditional Table Creation 224
 QCTC 3 Conditional Table Creation 243
 QCTC 3 Conditional Table Creation 262
 QCTC 3 Conditional Table Creation 298
 QCTC 3 Conditional Table Creation 312
 QCTC 3 Conditional Table Creation 369
 QCTC 3 Conditional Table Creation 398
 QCTC 3 Conditional Table Creation 428
 QCTC 3 Conditional Table Creation 467
 QGWO 3 Group by/Distinct/Union without order by 105
 QGWO 3 Group by/Distinct/Union without order by 356
 QGWO 3 Group by/Distinct/Union without order by 467
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 105
 CRDO 2 Read Only Cursor Marker (has a 'distinct' option) 356
 MTR1 2 Metrics: Comments Ratio Comments: 25% 13
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 23 = 42dec - 21exi + 2 13
 MTR3 2 Metrics: Query Complexity Complexity: 269 13

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#temptable9 (1) 
calls proc sybsystemprocs..sp_autoformat  
   writes table sybsystemprocs..sp_autoformat_rset_002 
   read_writes table tempdb..#colinfo_af (1) 
   writes table sybsystemprocs..sp_autoformat_rset_004 
   calls proc sybsystemprocs..sp_autoformat  
   reads table master..syscolumns (1)  
   reads table tempdb..systypes (1)  
   writes table sybsystemprocs..sp_autoformat_rset_001 
   reads table tempdb..syscolumns (1)  
   writes table sybsystemprocs..sp_autoformat_rset_003 
   calls proc sybsystemprocs..sp_namecrack  
   writes table sybsystemprocs..sp_autoformat_rset_005 
   reads table master..systypes (1)  
reads table master..sysoptions (1)  
writes table tempdb..#temptable11 (1) 
writes table tempdb..#temptable5 (1) 
read_writes table tempdb..#temptable10 (1) 
writes table tempdb..#temptable1 (1) 
writes table tempdb..#temptable6 (1) 
writes table tempdb..#temptable2 (1) 
writes table tempdb..#temptable7 (1) 
writes table tempdb..#temptable (1) 
writes table tempdb..#temptable4 (1) 
read_writes table tempdb..#temptable8 (1) 
writes table tempdb..#temptable3 (1) 
read_writes table tempdb..#cachetable (1)