Database | Proc | Application | Created | Links |
sybsystemprocs | sp_multdb_show ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** Messages for "sp_multdb_show" 5 ** 6 ** 17260, "Can't run %1! from within a transaction." 7 */ 8 9 /* 10 ** Procedure sp_multdb_show 11 ** 12 ** This procedure displays information stored in SYSATTRIBUTES 13 ** for the MULTEMPDB_CLASS (class 16) 14 ** 15 */ 16 create procedure sp_multdb_show 17 @option varchar(10) = NULL, 18 @name varchar(255) = NULL 19 20 as 21 22 declare 23 @loginname varchar(255), /* login name */ 24 @appname varchar(255), /* application name */ 25 @type int, /* binding type 'GR' or 'DB' */ 26 @groupid int, /* tempdb group id */ 27 @groupname varchar(255), /* tempdb group name */ 28 @dbname varchar(255), /* database name */ 29 @creategroups int, /* need the groups table */ 30 @createdbs int, /* need the dbs table */ 31 @createbind int, /* need the bindings table */ 32 @membership_list varchar(255), 33 @groupmember_list varchar(255), 34 @login_binding_list varchar(255), 35 @app_binding_list varchar(255), 36 @isSDC int, 37 @instanceid int, 38 @instancename varchar(255), 39 @sqlbuf varchar(255), 40 @retstat int, 41 @grptdb_stat int /* status for temp db that could be 42 ** added to a group, in smp, this is 43 ** user create temp db, in sdc, this 44 ** means local user temp db. 45 */ 46 /* 47 ** if we're in a transaction, disallow this since it might make recovery 48 ** impossible. 49 */ 50 if @@trancount > 0 51 begin 52 raiserror 17260, "sp_multdb_show" 53 return (1) 54 end 55 else 56 begin 57 /* Use TSQL mode of unchained transactions */ 58 set chained off 59 end 60 61 /* Don't do "Dirty Reads" */ 62 set transaction isolation level 1 63 64 /* initialize variables */ 65 select @creategroups = 0 66 select @createdbs = 0 67 select @createbind = 0 68 69 select @isSDC = case 70 when @@clustermode = "shared disk cluster" then 1 71 else 0 72 end 73 74 if (@isSDC = 1) 75 select @grptdb_stat = number 76 from master.dbo.spt_values 77 where type = "D3" and name = "local user temp db" 78 else 79 select @grptdb_stat = number 80 from master.dbo.spt_values 81 where type = "D3" and name = "user created temp db" 82 83 /* do we need the groups table */ 84 if (@option in ("all", "gr", NULL)) 85 select @creategroups = 1 86 87 /* do we need the databases table */ 88 if (@option in ("all", "gr", "db", NULL)) 89 select @createdbs = 1 90 91 /* do we need the bindings table */ 92 if (@option in ("all", "login", "app", NULL)) 93 select @createbind = 1 94 95 if (@creategroups = 1) 96 begin 97 /* stores groups */ 98 create table #multdb_groups 99 (groupname varchar(255), 100 groupid int) 101 end 102 103 if (@createdbs = 1) 104 begin 105 /* stores database to group membership */ 106 create table #multdb_dbs 107 (dbname varchar(255), 108 instancename varchar(255) null, 109 groupname varchar(255)) 110 end 111 112 if (@createbind = 1) 113 begin 114 /* stores login/application bindings */ 115 create table #multdb_bindings 116 (loginname varchar(255) null, 117 app varchar(255) null, 118 groupname varchar(255) null, 119 dbname varchar(255) null, 120 instancename varchar(255) null, 121 hardness varchar(6) null) 122 end 123 124 if (@creategroups = 1) 125 begin 126 /* populate groups */ 127 insert into #multdb_groups(groupname, groupid) 128 select object_cinfo, int_value 129 from master..sysattributes 130 where class = 16 131 AND attribute = 0 132 AND object_type = 'GR' 133 end 134 135 if (@createdbs = 1) 136 begin 137 /* 138 ** The system tempdb is implicitly part of the 139 ** default group. So as long as the default group 140 ** has been created we will report the tempdb as 141 ** belonging to that group. 142 */ 143 if @isSDC = 0 and 144 exists (select * 145 from master..sysattributes 146 where class = 16 147 AND attribute = 0 148 AND object_type = 'GR' 149 AND object_cinfo = "default") 150 begin 151 insert into #multdb_dbs(dbname, groupname) 152 values ("tempdb", "default") 153 end 154 155 insert into #multdb_dbs(dbname, instancename, groupname) 156 select a.object_cinfo, instance_name(a.object_info2), b.object_cinfo 157 from master..sysattributes a, master..sysattributes b 158 where a.class = 16 159 AND b.class = 16 160 AND a.object_type = 'D ' 161 AND b.object_type = 'GR' 162 AND a.object = b.int_value 163 /* Inserting the names of temporary databases which are not added 164 ** to any group 165 */ 166 insert into #multdb_dbs(dbname, groupname) 167 select name, " " from master..sysdatabases 168 where status3 & @grptdb_stat != 0 AND name NOT IN 169 (select dbname from #multdb_dbs) 170 171 end 172 173 if (@createbind = 1) 174 begin 175 /* login binding to group */ 176 insert into #multdb_bindings(loginname, groupname, hardness) 177 select a.name, b.char_value, 178 case 179 when b.object_info1 = 1 then 'HARD' 180 else 'SOFT' 181 end 182 from master..syslogins a, master..sysattributes b 183 where b.class = 16 184 AND b.object_cinfo is NULL 185 AND b.attribute = 1 186 AND (b.object_type = 'LG' OR b.object_type = 'AP') 187 AND a.suid = b.object 188 AND b.int_value = 1 189 190 /* login binding to DB */ 191 insert into #multdb_bindings(loginname, dbname, instancename, hardness) 192 select a.name, b.char_value, 193 case 194 when @isSDC = 1 then instance_name(b.object_info2) 195 else NULL 196 end, 197 case 198 when b.object_info1 = 1 then 'HARD' 199 else 'SOFT' 200 end 201 from master..syslogins a, master..sysattributes b 202 where b.class = 16 203 AND b.object_cinfo is NULL 204 AND b.attribute = 1 205 AND (b.object_type = 'LG' OR b.object_type = 'AP') 206 AND a.suid = b.object 207 AND b.int_value = 0 208 209 /* application binding to group */ 210 insert into #multdb_bindings(app, groupname, hardness) 211 select a.object_cinfo, a.char_value, 212 case 213 when a.object_info1 = 1 then 'HARD' 214 else 'SOFT' 215 end 216 from master..sysattributes a 217 where a.class = 16 218 AND (a.object_type = 'LG' OR a.object_type = 'AP') 219 AND a.object is NULL 220 AND a.attribute = 1 221 AND a.int_value = 1 222 223 /* application binding to DB */ 224 insert into #multdb_bindings(app, dbname, instancename, hardness) 225 select a.object_cinfo, a.char_value, 226 case 227 when @isSDC = 1 then instance_name(a.object_info2) 228 else NULL 229 end, 230 case 231 when a.object_info1 = 1 then 'HARD' 232 else 'SOFT' 233 end 234 from master..sysattributes a 235 where a.class = 16 236 AND (a.object_type = 'LG' OR a.object_type = 'AP') 237 AND a.object is NULL 238 AND a.attribute = 1 239 AND a.int_value = 0 240 241 242 /* application with login scope binding to group */ 243 insert into #multdb_bindings(loginname, app, groupname, hardness) 244 select a.name, b.object_cinfo, b.char_value, 245 case 246 when b.object_info1 = 1 then 'HARD' 247 else 'SOFT' 248 end 249 from master..syslogins a, master..sysattributes b 250 where b.class = 16 251 AND b.attribute = 1 252 AND (b.object_type = 'LG' OR b.object_type = 'AP') 253 AND a.suid = b.object 254 AND b.object_cinfo is not NULL 255 AND b.object is not NULL 256 AND b.int_value = 1 257 258 /* application with login scope binding to DB */ 259 insert into #multdb_bindings(loginname, app, dbname, 260 instancename, hardness) 261 select a.name, b.object_cinfo, b.char_value, 262 case 263 when @isSDC = 1 then instance_name(b.object_info2) 264 else NULL 265 end, 266 case 267 when b.object_info1 = 1 then 'HARD' 268 else 'SOFT' 269 end 270 from master..syslogins a, master..sysattributes b 271 where b.class = 16 272 AND b.attribute = 1 273 AND (b.object_type = 'LG' OR b.object_type = 'AP') 274 AND a.suid = b.object 275 AND b.object_cinfo is not NULL 276 AND b.object is not NULL 277 AND b.int_value = 0 278 279 end 280 281 select @membership_list = "'Database' = dbname,'GroupName' = groupname" 282 select @groupmember_list = "'Member Databases' = dbname" 283 select @login_binding_list = "'Login' = loginname,'Application' = app,\ 284 'Group' = groupname,'Database' = dbname,\ 285 'Hardness' = hardness" 286 select @app_binding_list = "'Application' = app,'Login' = loginname, \ 287 'Group' = groupname, 'Database' = dbname, \ 288 'Hardness' = hardness" 289 290 291 292 293 if (@option is NULL) or (@option = "all") 294 begin 295 /* Print list of database groups */ 296 exec sp_autoformat @fulltabname = #multdb_groups, 297 @selectlist = "'Temporary Database Groups' = groupname" 298 299 /* Print database to group membership */ 300 print " " 301 exec sp_autoformat @fulltabname = #multdb_dbs, 302 @selectlist = @membership_list 303 304 /* Print information about each binding */ 305 print " " 306 exec sp_autoformat @fulltabname = #multdb_bindings, 307 @selectlist = @login_binding_list 308 print " " 309 end 310 311 if (@option = "gr") 312 begin 313 if (@name is NULL) 314 begin 315 /* Print list of database groups */ 316 exec sp_autoformat @fulltabname = #multdb_groups, 317 @selectlist = "'Temporary Database Groups' = groupname" 318 end 319 else 320 begin 321 /* Print databases bound to this group */ 322 select dbname, instancename 323 into #multdb_dbs1rs 324 from #multdb_dbs 325 where groupname = @name 326 exec sp_autoformat @fulltabname = #multdb_dbs1rs, 327 @selectlist = @groupmember_list 328 drop table #multdb_dbs1rs 329 end 330 end 331 332 333 if (@option = "db") 334 begin 335 if (@name is NULL) 336 begin 337 /* Print database to group membership */ 338 print " " 339 exec sp_autoformat @fulltabname = #multdb_dbs, 340 @selectlist = @membership_list 341 end 342 else 343 begin 344 /* Print database to group membership for given database */ 345 print " " 346 select dbname, instancename, groupname 347 into #multdb_dbs2rs 348 from #multdb_dbs 349 where dbname = @name 350 exec sp_autoformat @fulltabname = #multdb_dbs2rs, 351 @selectlist = @membership_list 352 drop table #multdb_dbs2rs 353 end 354 end 355 356 if (@option = "login") 357 begin 358 if (@name is NULL) 359 begin 360 print " " 361 exec sp_autoformat @fulltabname = #multdb_bindings, 362 @selectlist = @login_binding_list, 363 @whereclause = "where loginname is not null" 364 end 365 else 366 begin 367 print " " 368 select loginname, app, groupname, dbname, instancename, 369 hardness 370 into #multdb_bindings1rs 371 from #multdb_bindings 372 where loginname = @name 373 exec sp_autoformat @fulltabname = #multdb_bindings1rs, 374 @selectlist = @login_binding_list 375 drop table #multdb_bindings1rs 376 end 377 end 378 379 if (@option = "app") 380 begin 381 if (@name is NULL) 382 begin 383 print " " 384 exec sp_autoformat @fulltabname = #multdb_bindings, 385 @selectlist = @app_binding_list, 386 @whereclause = "where app is not null" 387 end 388 else 389 begin 390 print " " 391 select app, loginname, groupname, dbname, instancename, 392 hardness 393 into #multdb_bindings2rs 394 from #multdb_bindings 395 where app = @name 396 exec sp_autoformat @fulltabname = #multdb_bindings2rs, 397 @selectlist = @app_binding_list 398 drop table #multdb_bindings2rs 399 end 400 end 401 402 if (@option = "who") 403 begin 404 if @@clustermode != "shared disk cluster" 405 begin 406 select "spid" = spid, "loginame" = suser_name(suid), 407 "application" = program_name 408 into #multdb_show1rs 409 from master..sysprocesses 410 where tempdb_id(spid) = db_id(@name) 411 exec sp_autoformat @fulltabname = #multdb_show1rs 412 drop table #multdb_show1rs 413 end 414 else 415 begin 416 417 select "spid" = spid, "loginame" = suser_name(suid), 418 419 "application" = program_name 420 421 into #multdb_show2rs 422 from master..sysprocesses 423 where 424 425 tempdb_id(spid) = db_id(@name) 426 exec sp_autoformat @fulltabname = #multdb_show2rs 427 drop table #multdb_show2rs 428 end 429 end 430 431 if (@creategroups = 1) 432 drop table #multdb_groups 433 if (@createdbs = 1) 434 drop table #multdb_dbs 435 if (@createbind = 1) 436 drop table #multdb_bindings 437 438 return (0) 439
DEFECTS | |
![]() | master..sysattributes |
![]() | 177 |
![]() | 192 |
![]() | 211 |
![]() | 225 |
![]() | 244 |
![]() | 261 |
![]() | 296 |
![]() | 301 |
![]() | 306 |
![]() | 316 |
![]() | 326 |
![]() | 339 |
![]() | 350 |
![]() | 361 |
![]() | 373 |
![]() | 384 |
![]() | 396 |
![]() | 411 |
![]() | 426 |
![]() (number, type) Intersection: {type} | 77 |
![]() (number, type) Intersection: {type} | 81 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object} Uncovered: [attribute, object_info1, object_info2, object_info3, object_cinfo] | 162 |
![]() | 130 |
![]() | 131 |
![]() | 146 |
![]() | 147 |
![]() | 158 |
![]() | 159 |
![]() | 183 |
![]() | 185 |
![]() | 202 |
![]() | 204 |
![]() | 217 |
![]() | 220 |
![]() | 235 |
![]() | 238 |
![]() | 250 |
![]() | 251 |
![]() | 271 |
![]() | 272 |
![]() | master..sysprocesses |
![]() | master..spt_values |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 127 |
![]() | 151 |
![]() | 155 |
![]() | 166 |
![]() | 176 |
![]() | 191 |
![]() | 210 |
![]() | 224 |
![]() | 243 |
![]() | 259 |
![]() | 296 |
![]() | 301 |
![]() | 306 |
![]() | 316 |
![]() | 322 |
![]() | 326 |
![]() | 339 |
![]() | 346 |
![]() | 350 |
![]() | 361 |
![]() | 368 |
![]() | 373 |
![]() | 384 |
![]() | 391 |
![]() | 396 |
![]() | 406 |
![]() | 411 |
![]() | 417 |
![]() | 426 |
![]() | 53 |
![]() | 74 |
![]() | 84 |
![]() | 88 |
![]() | 92 |
![]() | 95 |
![]() | 103 |
![]() | 112 |
![]() | 124 |
![]() | 135 |
![]() | 173 |
![]() | 311 |
![]() | 313 |
![]() | 333 |
![]() | 335 |
![]() | 356 |
![]() | 358 |
![]() | 379 |
![]() | 381 |
![]() | 402 |
![]() | 431 |
![]() | 433 |
![]() | 435 |
![]() | 438 |
![]() | 98 |
![]() | 106 |
![]() | 75 |
![]() | 79 |
![]() | 98 |
![]() | 106 |
![]() | 115 |
![]() | 322 |
![]() | 346 |
![]() | 368 |
![]() | 391 |
![]() | 406 |
![]() | 417 |
![]() | 62 |
![]() | 151 |
![]() | 166 |
![]() | 176 |
![]() | 191 |
![]() | 210 |
![]() | 224 |
![]() | 243 |
![]() | 259 |
![]() | 168 |
![]() | 157 |
![]() | 182 |
![]() | 201 |
![]() | 249 |
![]() | 270 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} | 130 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {object_type, object_cinfo, attribute, class} | 146 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type} | 159 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_type, object_cinfo, class} | 183 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_type, object_cinfo, class} | 202 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_type, object, class} | 217 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_type, object, class} | 235 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_type, object_cinfo, object, class} | 250 |
![]() (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {attribute, object_type, object_cinfo, object, class} | 271 |
![]() | 23 |
![]() | 24 |
![]() | 25 |
![]() | 26 |
![]() | 27 |
![]() | 28 |
![]() | 37 |
![]() | 38 |
![]() | 39 |
![]() | 40 |
![]() | 144 |
![]() | 16 |
![]() | 16 |
![]() | 16 |
![]() | 156 |
![]() | 177 |
![]() | 192 |
![]() | 244 |
![]() | 261 |
DEPENDENCIES |
PROCS AND TABLES USED writes table tempdb..#multdb_bindings2rs (1) reads table master..sysprocesses (1) ![]() writes table tempdb..#multdb_show2rs (1) reads table master..spt_values (1) ![]() writes table tempdb..#multdb_groups (1) writes table tempdb..#multdb_show1rs (1) reads table master..sysattributes (1) ![]() writes table tempdb..#multdb_bindings1rs (1) writes table tempdb..#multdb_dbs1rs (1) read_writes table tempdb..#multdb_dbs (1) calls proc sybsystemprocs..sp_autoformat ![]() read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_namecrack ![]() reads table tempdb..systypes (1) ![]() calls proc sybsystemprocs..sp_autoformat ![]() reads table master..systypes (1) ![]() reads table master..syscolumns (1) ![]() reads table tempdb..syscolumns (1) ![]() writes table tempdb..#multdb_dbs2rs (1) reads table master..syslogins (1) ![]() read_writes table tempdb..#multdb_bindings (1) reads table master..sysdatabases (1) ![]() CALLERS called by proc sybsystemprocs..sp_tempdb ![]() |