| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_options | | 31 Aug 14 | Defects 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 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
| DEPENDENCIES |
| PROCS AND TABLES USED writes table tempdb..#temptable7 (1) reads table master..sysoptions (1)  writes table tempdb..#temptable6 (1) writes table tempdb..#temptable1 (1) writes table tempdb..#temptable (1) writes table tempdb..#temptable11 (1) writes table tempdb..#temptable3 (1) read_writes table tempdb..#temptable10 (1) read_writes table tempdb..#temptable9 (1) writes table tempdb..#temptable2 (1) calls proc sybsystemprocs..sp_autoformat  calls proc sybsystemprocs..sp_namecrack calls proc sybsystemprocs..sp_autoformat reads table master..systypes (1) reads table master..syscolumns (1) read_writes table tempdb..#colinfo_af (1) reads table tempdb..syscolumns (1)  reads table tempdb..systypes (1) read_writes table tempdb..#temptable8 (1) writes table tempdb..#temptable5 (1) writes table tempdb..#temptable4 (1) read_writes table tempdb..#cachetable (1) |
