| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_helpdevice | | 31 Aug 14 | Defects Dependencies |
1 create procedure sp_helpdevice 2 @devname varchar(255) = "%" /* device to check out */ 3 as 4 5 declare @tapeblocksize int 6 7 declare @msg varchar(1024) 8 declare @sptlang int 9 declare @length int 10 11 12 if @@trancount = 0 13 begin 14 set chained off 15 end 16 17 set transaction isolation level 1 18 19 select @sptlang = @@langid 20 21 if @@langid != 0 22 begin 23 if not exists ( 24 select * from master.dbo.sysmessages where error 25 between 17120 and 17129 26 and langid = @@langid) 27 select @sptlang = 0 28 end 29 30 /* 31 ** See if the device exists. 32 */ 33 if not exists (select * 34 from master.dbo.sysdevices 35 where name like @devname) 36 begin 37 /* 17610, "No such i/o device exists." */ 38 raiserror 17610 39 return (1) 40 end 41 42 /* 43 ** Create a temporary table where we can build up a translation of 44 ** the device status bits. 45 */ 46 create table #spdevtab 47 ( 48 dbdev_flag smallint, 49 imdbdev_flag int, 50 vpn_low int, 51 vpn_high int, 52 name varchar(255), 53 vdevno int, 54 statusdesc varchar(1024) null 55 ) 56 57 set nocount on 58 59 /* 60 ** Initialize the temporary table with - 61 ** - names of the devices. 62 ** - null (no description yet) 63 ** - the device's low page number 64 ** - the device's high page number 65 ** - a flag stating whether this is a database device 66 */ 67 insert into #spdevtab(dbdev_flag, imdbdev_flag, vpn_low, 68 vpn_high, name, vdevno) 69 select status & 2, status2 & 8, low, high, name, vdevno 70 from master.dbo.sysdevices 71 where name like @devname 72 73 /* 74 ** Now figure out what kind of device it is 75 ** 1 - raw device 76 ** 2 - block device 77 ** 3 - file system device 78 */ 79 80 /* 17628, "unknown device type" */ 81 exec sp_getmessage 17628, @msg out 82 update #spdevtab set statusdesc = @msg 83 84 /* 17631, "virtual cache device" */ 85 exec sp_getmessage 17631, @msg out 86 update #spdevtab 87 set statusdesc = @msg 88 from master.dbo.sysdevices d, #spdevtab 89 where d.status2 & 8 = 8 90 and #spdevtab.name = d.name 91 92 /* 17625, "raw device" */ 93 exec sp_getmessage 17625, @msg out 94 update #spdevtab 95 set statusdesc = @msg 96 from master.dbo.sysdevices d, #spdevtab 97 where 1 in (select getdevicetype(d.phyname)) 98 and #spdevtab.name = d.name 99 100 /* 17626, "block device" */ 101 exec sp_getmessage 17626, @msg out 102 update #spdevtab 103 set statusdesc = @msg 104 from master.dbo.sysdevices d, #spdevtab 105 where 2 in (select getdevicetype(d.phyname)) 106 and #spdevtab.name = d.name 107 108 /* 17627, " file system device" */ 109 exec sp_getmessage 17627, @msg out 110 update #spdevtab 111 set statusdesc = @msg 112 from master.dbo.sysdevices d, #spdevtab 113 where 3 in (select getdevicetype(d.phyname)) 114 and #spdevtab.name = d.name 115 116 /* 117 ** Now figure out what kind of controller type it is. The type are 118 ** COMPLETELY machine dependent and are for UNIX. 119 ** cntrltype = 0 special 120 ** 2 disk 121 ** 3-8 tape 122 ** 9-14 channel 123 */ 124 125 /* 17611, "special" */ 126 exec sp_getmessage 17611, @msg out 127 update #spdevtab 128 set statusdesc = statusdesc + ", " + @msg 129 from master.dbo.sysdevices d, #spdevtab 130 where d.cntrltype = 0 131 and #spdevtab.name = d.name 132 133 /* 17612, "disk" */ 134 exec sp_getmessage 17612, @msg out 135 update #spdevtab 136 set statusdesc = statusdesc + ", " + @msg 137 from master.dbo.sysdevices d, #spdevtab 138 where d.cntrltype = 2 139 and #spdevtab.name = d.name 140 141 /* 17613, "tape" */ 142 exec sp_getmessage 17613, @msg out 143 update #spdevtab 144 set statusdesc = statusdesc + ", " + @msg 145 from master.dbo.sysdevices d, #spdevtab 146 where d.cntrltype >= 3 and d.cntrltype <= 8 147 and #spdevtab.name = d.name 148 /* 17619, "channel" */ 149 exec sp_getmessage 17619, @msg out 150 update #spdevtab 151 set statusdesc = statusdesc + ", " + @msg 152 from master.dbo.sysdevices d, #spdevtab 153 where d.cntrltype >= 9 and d.cntrltype <= 14 154 and #spdevtab.name = d.name 155 156 /* 157 ** If a tape device, also figure out the tape capacity which is listed 158 ** in sysdevices.high in number of 32k blocks. 159 */ 160 if exists (select * 161 from master.dbo.sysdevices d, #spdevtab 162 where d.cntrltype >= 3 and d.cntrltype <= 8 163 and d.high > 0 164 and #spdevtab.name = d.name) 165 begin 166 /* 167 ** Find size of tape block. Default to 32k. 168 */ 169 170 select @tapeblocksize = low 171 from master.dbo.spt_values 172 where type = "E" 173 and number = 4 174 if @tapeblocksize is NULL 175 begin 176 select @tapeblocksize = 32768 177 end 178 179 update #spdevtab 180 set statusdesc = statusdesc + ", " + str(round((d.high * 181 convert(float, @tapeblocksize)) / 1048576., 0)) 182 + " MB" 183 from master.dbo.sysdevices d, #spdevtab 184 where d.cntrltype >= 3 and d.cntrltype <= 8 185 and d.high > 0 186 and #spdevtab.name = d.name 187 end 188 189 /* 17614, "UNKNOWN DEVICE" */ 190 exec sp_getmessage 17614, @msg out 191 update #spdevtab 192 set statusdesc = statusdesc + ", " + @msg 193 from master.dbo.sysdevices d, #spdevtab 194 where d.cntrltype > 14 195 and #spdevtab.name = d.name 196 197 198 199 /* 200 ** Now check out the status bits and turn them into english. 201 ** The mirror status bits after masking with 16383 are >= 32. 202 ** The 0x3fff mask is necessary since ASE12.0 introduced a 203 ** new status bit 0x4000 to indicate the dsync option. 204 */ 205 206 207 if exists (select * 208 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 209 where v.type = "V" and v.number > - 1 210 and (d.status & 16383) >= 32 211 and d.status & 256 != 256 212 and #spdevtab.name = d.name) 213 begin 214 /* 215 ** Check to see if the mirror is enabled. 0x200 (512) bit in status. 216 */ 217 if exists (select * 218 from master.dbo.sysdevices d, #spdevtab 219 where d.status & 512 = 512 220 and #spdevtab.name = d.name) 221 begin 222 /* 17615, "MIRROR ENABLED" */ 223 exec sp_getmessage 17615, @msg out 224 update #spdevtab 225 set statusdesc = statusdesc + ", " + @msg 226 from master.dbo.sysdevices d, #spdevtab 227 where d.status & 512 = 512 228 and #spdevtab.name = d.name 229 end 230 231 if exists (select * 232 from master.dbo.sysdevices d, #spdevtab 233 where d.status & 512 != 512 234 and (d.status & 16383) >= 32 235 and #spdevtab.name = d.name) 236 begin 237 /* 17616, "MIRROR DISABLED" */ 238 exec sp_getmessage 17616, @msg out 239 update #spdevtab 240 set statusdesc = statusdesc + ", " + @msg 241 from master.dbo.sysdevices d, #spdevtab 242 where d.status & 512 != 512 243 and (d.status & 16383) >= 32 244 and #spdevtab.name = d.name 245 end 246 247 /* add mirror name.*/ 248 249 /* 250 ** NOTE: Do not include this for private devices as there's no mirroring 251 ** done for private device due to which we'll always see text as follows 252 ** ... MIRROR DISABLED, mirror = ''... 253 */ 254 255 /* 17617, "mirror = " */ 256 exec sp_getmessage 17617, @msg out 257 update #spdevtab 258 set statusdesc = statusdesc + ", " + @msg + " '" + d.mirrorname 259 + "'" 260 from master.dbo.sysdevices d, #spdevtab 261 where #spdevtab.name = d.name 262 and (d.status & 16383) >= 32 263 and (d.status2 & 2) != 2 264 end 265 266 /* 267 ** Check to see if there is a mirrorname entry but mirroring not enabled. 268 ** If so, then one side of the mirror is off-line. 269 */ 270 else if exists (select * 271 from master.dbo.sysdevices d, #spdevtab 272 where #spdevtab.name = d.name 273 and d.status & 256 = 256 274 and d.mirrorname is not null) 275 begin 276 /* Set up the message */ 277 declare @part1 varchar(60) 278 declare @part2 varchar(60) 279 declare @part3 varchar(60) 280 /* 17618, "only device '%1!' of mirror is enabled -- device '%2!' 281 ** is disabled" 282 */ 283 exec sp_getmessage 17618, @msg out 284 select @part1 = substring(@msg, 1, charindex("%1!", @msg) - 1) 285 select @part2 = substring(@msg, charindex("%1!", @msg) + 3, 286 ((charindex("%2!", @msg) - 1) - (charindex("%1!", @msg) + 2))) 287 select @part3 = substring(@msg, charindex("%2!", @msg) + 3, 60) 288 289 /* 290 ** Figure out which side of the mirror is disabled. 291 ** If 0x100 is on then phyname is disabled and mirrorname is enabled. 292 */ 293 if exists (select * 294 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 295 where v.type = "V" and v.number > - 1 296 and d.status & v.number = 256 297 and #spdevtab.name = d.name) 298 begin 299 update #spdevtab 300 set statusdesc = @part1 + d.mirrorname 301 + @part2 + d.phyname + @part3 302 from master.dbo.sysdevices d, master.dbo.spt_values v, 303 #spdevtab 304 where v.type = "V" and v.number > - 1 305 and d.status & v.number = 256 306 and #spdevtab.name = d.name 307 end 308 else if exists (select * 309 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 310 where v.type = "V" and v.number > - 1 311 and d.status & v.number = 64 312 and #spdevtab.name = d.name) 313 begin 314 update #spdevtab 315 set statusdesc = @part1 + d.phyname 316 + @part2 + d.mirrorname + @part3 317 from master.dbo.sysdevices d, master.dbo.sysdevices e, 318 master.dbo.spt_values v, #spdevtab 319 where v.type = "V" and v.number > - 1 320 and d.status & v.number = 64 321 and #spdevtab.name = d.name 322 and e.status & 256 != 256 323 and #spdevtab.name = e.name 324 end 325 end 326 327 328 /* 329 ** Status of 0x20 is "serial writes" for mirrored disks. 330 */ 331 if exists (select * 332 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 333 where v.type = "V" and v.number > - 1 334 and d.status & v.number = 32 335 and #spdevtab.name = d.name) 336 begin 337 update #spdevtab 338 set statusdesc = statusdesc + ", " + m.description 339 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab, 340 master.dbo.sysmessages m 341 where v.type = "V" and v.number > - 1 342 and d.status & v.number = 32 343 and #spdevtab.name = d.name 344 and v.msgnum = m.error 345 and isnull(m.langid, 0) = @sptlang 346 end 347 348 /* 17620, "nonserial writes" */ 349 exec sp_getmessage 17620, @msg out 350 351 /* 352 ** "nonserial writes" only relevant for mirrorred devices. 353 ** The 0x3fff mask is necessary since ASE12.0 introduced a 354 ** new status bit 0x4000 to indicate the dsync option. 355 */ 356 357 update #spdevtab 358 set statusdesc = statusdesc + ", " + @msg 359 from master.dbo.sysdevices d, #spdevtab 360 where (d.status & 16383) > 32 361 and d.status & 32 != 32 362 and #spdevtab.name = d.name 363 364 /* 365 ** Check for the dsync option : 366 ** The status bit 16384 (0x4000) sets the dsync option for the disk_device only 367 */ 368 369 begin 370 /* 371 ** For all devices, check to see if the 'dsync' bit is on. 372 ** 'master' and 'master_companion' (in HA failover mode), 373 ** always have the 'dsync' bit on. The use of @@cmpstate here is 374 ** to distinguish a master_companion device from a HA failover 375 ** and a non-HA user created one. It is possible for user to 376 ** create a device named "master_companion" in a non-HA ASE. 377 */ 378 /* 17621, "dsync on" */ 379 exec sp_getmessage 17621, @msg out 380 update #spdevtab 381 set statusdesc = statusdesc + ", " + @msg 382 from master.dbo.sysdevices d, #spdevtab 383 where (d.status & 16384 = 16384 384 or (d.name = "master" 385 or (d.name = "master_companion" 386 and @@cmpstate in (4, 12)))) 387 and d.status & 2 = 2 388 and #spdevtab.name = d.name 389 390 /* 17622, "dsync off" */ 391 exec sp_getmessage 17622, @msg out 392 update #spdevtab 393 set statusdesc = statusdesc + ", " + @msg 394 from master.dbo.sysdevices d, #spdevtab 395 where (d.status & 16384 != 16384) 396 and d.status & 2 = 2 397 and #spdevtab.name = d.name 398 and not (d.name = "master" 399 or (d.name = "master_companion" 400 and @@cmpstate in (4, 12))) 401 402 end 403 404 /* 17623, "directio on" */ 405 exec sp_getmessage 17623, @msg out 406 update #spdevtab 407 set statusdesc = statusdesc + ", " + @msg 408 from master.dbo.sysdevices d, #spdevtab 409 where d.status2 & 1 = 1 410 and d.status & 2 = 2 411 and #spdevtab.name = d.name 412 413 /* 17624, "directio off" */ 414 exec sp_getmessage 17624, @msg out 415 update #spdevtab 416 set statusdesc = statusdesc + ", " + @msg 417 from master.dbo.sysdevices d, #spdevtab 418 where d.status2 & 1 != 1 419 and d.status & 2 = 2 420 and #spdevtab.name = d.name 421 422 /* 423 ** Status of 0x80 is "reads mirrored" for mirrored disks. 424 */ 425 if exists (select * 426 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 427 where v.type = "V" and v.number > - 1 428 and d.status & v.number = 128 429 and #spdevtab.name = d.name) 430 begin 431 update #spdevtab 432 set statusdesc = statusdesc + ", " + m.description 433 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab, 434 master.dbo.sysmessages m 435 where v.type = "V" and v.number > - 1 436 and d.status & v.number = 128 437 and #spdevtab.name = d.name 438 and v.msgnum = m.error 439 and isnull(m.langid, 0) = @sptlang 440 end 441 442 /* 443 ** Now check out the status bits and turn them into english. 444 ** Status of 0x10 is a dump device. 445 */ 446 if exists (select * 447 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 448 where v.type = "V" and v.number > - 1 449 and d.status & v.number = 16 450 and #spdevtab.name = d.name) 451 begin 452 update #spdevtab 453 set statusdesc = statusdesc + ", " + m.description 454 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab, 455 master.dbo.sysmessages m 456 where v.type = "V" and v.number > - 1 457 and d.status & v.number = 16 458 and #spdevtab.name = d.name 459 and v.msgnum = m.error 460 and isnull(m.langid, 0) = @sptlang 461 end 462 463 /* 464 ** Now check out the status bits and turn them into english. 465 ** Status of 0x01 is a default disk. 466 */ 467 if exists (select * 468 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 469 where v.type = "V" and v.number > - 1 470 and d.status & v.number = 1 471 and #spdevtab.name = d.name) 472 begin 473 update #spdevtab 474 set statusdesc = statusdesc + ", " + m.description 475 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab, 476 master.dbo.sysmessages m 477 where v.type = "V" and v.number > - 1 478 and d.status & v.number = 1 479 and #spdevtab.name = d.name 480 and v.msgnum = m.error 481 and isnull(m.langid, 0) = @sptlang 482 end 483 484 /* 485 ** Now check out the status bits and turn them into english. 486 ** Status of 0x02 is a physical disk Status2 0x08 is virtual 487 ** cache device used for in-memory databases. 488 */ 489 if exists (select * 490 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 491 where ((v.type = "V" and v.number > - 1 492 and (d.status & v.number = 2)) 493 or (v.type = "V2" and v.number > - 1 494 and (d.status2 & v.number = 8))) 495 and #spdevtab.name = d.name) 496 begin 497 update #spdevtab 498 set statusdesc = statusdesc + ", " + m.description 499 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab, 500 master.dbo.sysmessages m 501 where v.type = "V" and v.number > - 1 502 and d.status & v.number = 2 503 and #spdevtab.name = d.name 504 and v.msgnum = m.error 505 and isnull(m.langid, 0) = @sptlang 506 507 /* 508 ** Compute number of Pages in a Megabyte. 509 */ 510 declare @numpgsmb float /* Number of 'virtual' Pages per Megabytes */ 511 512 select @numpgsmb = (1048576. / @@pagesize) 513 514 /* 515 ** Add in its size in MB. 516 */ 517 update #spdevtab 518 set statusdesc = statusdesc + ", " + 519 ltrim(str((1. + (d.high - d.low)) / @numpgsmb, 10, 2)) + " MB" 520 from master.dbo.sysdevices d, #spdevtab 521 where ((d.status & 2 = 2) or (d.status2 & 8 = 8)) 522 and #spdevtab.name = d.name 523 524 /* 525 ** Calculate unused size in MB. 526 */ 527 select d.vdevno, usedsizeMB = isnull((sum(u.size) / 512.0) * (@@maxpagesize / @@pagesize), 0) 528 into #spdevusedtab 529 from master.dbo.sysdevices d, master.dbo.sysusages u 530 where u.vdevno =* d.vdevno 531 and ((d.status & 2 = 2) or (d.status2 & 8 = 8)) 532 group by d.vdevno 533 534 update #spdevtab 535 set statusdesc = statusdesc + ", Free: " + 536 ltrim(str(((1. + (d.high - d.low)) / @numpgsmb) - usedsizeMB, 10, 2)) + " MB" 537 from master.dbo.sysdevices d, #spdevusedtab u, #spdevtab 538 where ((d.status & 2 = 2) or (d.status2 & 8 = 8)) 539 and d.vdevno = u.vdevno 540 and #spdevtab.name = d.name 541 542 end 543 544 /* 545 ** Now check out the status bits and turn them into english. 546 ** Status of 0x04 is a archive database disk. 547 */ 548 if exists (select * 549 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 550 where v.type = "V" and v.number > - 1 551 and d.status & v.number = 4 552 and #spdevtab.name = d.name) 553 begin 554 update #spdevtab 555 set statusdesc = statusdesc + ", " + m.description 556 from master.dbo.sysdevices d, master.dbo.spt_values v, 557 #spdevtab, master.dbo.sysmessages m 558 where v.type = "V" and v.number > - 1 559 and d.status & v.number = 4 560 and #spdevtab.name = d.name 561 and v.msgnum = m.error 562 and isnull(m.langid, 0) = @sptlang 563 end 564 565 /* 566 ** Now check out the status bits and turn them into english. 567 ** Status of 0x08 is a read-only disk device. 568 */ 569 if exists (select * 570 from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab 571 where v.type = "V" and v.number > - 1 572 and d.status & v.number = 8 573 and #spdevtab.name = d.name) 574 begin 575 update #spdevtab 576 set statusdesc = statusdesc + ", " + m.description 577 from master.dbo.sysdevices d, master.dbo.spt_values v, 578 #spdevtab, master.dbo.sysmessages m 579 where v.type = "V" and v.number > - 1 580 and d.status & v.number = 8 581 and #spdevtab.name = d.name 582 and v.msgnum = m.error 583 and isnull(m.langid, 0) = @sptlang 584 end 585 586 set nocount off 587 588 /* 589 ** Display the device info 590 */ 591 592 select device_name = d.name, 593 physical_name = d.phyname, 594 description = #spdevtab.statusdesc, 595 d.status, d.cntrltype, 596 d.vdevno, vpn_low, vpn_high 597 into #sphelpdevice1rs 598 from master.dbo.sysdevices d, #spdevtab 599 where d.name = #spdevtab.name 600 601 exec sp_autoformat @fulltabname = #sphelpdevice1rs, 602 @orderby = "order by device_name" 603 drop table #sphelpdevice1rs 604 605 /* 606 ** Display space allocation per database if called for a single device 607 */ 608 set nocount on 609 610 if (select count(*) from #spdevtab 611 where ((dbdev_flag = 2) or (imdbdev_flag = 8))) = 1 612 begin 613 select dbname = db_name(dbid), 614 size = str(((size / 512.0) * (@@maxpagesize / @@pagesize)), 10, 2) + " MB", 615 allocated = crdate, 616 vstart, lstart 617 into #spdevfragtab 618 from master.dbo.sysusages 619 where vdevno = (select vdevno from #spdevtab 620 where ((dbdev_flag = 2) or (imdbdev_flag = 8))) 621 622 exec sp_autoformat @fulltabname = #spdevfragtab, 623 @orderby = "order by allocated, vstart" 624 end 625 626 set nocount off 627 628 /* 629 ** If the server is booted with the trace flag 1624 on, then 'dsync' 630 ** will be turned off for the master device. 631 ** 632 ** If the server is booted with the trace flag 1623 on, then 'dsync' 633 ** will be turned off for all the devices. 634 ** 635 ** Report a warning message to the user if the server is booted 636 ** with the trace flag 1623 or 1624 turned . 637 ** 638 ** Note: The warning messages generated will be in English only, as 639 ** these trace flags are for internal testing only. 640 */ 641 declare @tracemsg varchar(255) 642 select @tracemsg = "WARNING: 'dsync is OFF' FOR %1! DUE TO THE TRACE FLAG %2!; OVERRIDING THE REAL STATUS IN SYSDEVICES" 643 dbcc istraceon(1624) 644 if @@error != - 1 645 begin 646 dbcc istraceon(1623) 647 if @@error = - 1 648 begin 649 print "" 650 print @tracemsg, "THE 'master'/'master_companion' DEVICE", 1624 651 end 652 end 653 654 dbcc istraceon(1623) 655 if @@error != - 1 656 begin 657 print "" 658 print @tracemsg, "ALL DEVICES", 1623 659 end 660 661 return (0) 662
exec sp_procxmode 'sp_helpdevice', 'AnyMode' go Grant Execute on sp_helpdevice to public go
| DEPENDENCIES |
| PROCS AND TABLES USED read_writes table tempdb..#spdevtab (1) writes table tempdb..#spdevfragtab (1) reads table master..sysusages (1)  calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1) reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..syslanguages (1) reads table master..spt_values (1) reads table master..sysmessages (1) read_writes table tempdb..#spdevusedtab (1) writes table tempdb..#sphelpdevice1rs (1) calls proc sybsystemprocs..sp_autoformat  read_writes table tempdb..#colinfo_af (1) calls proc sybsystemprocs..sp_namecrack reads table tempdb..syscolumns (1)  calls proc sybsystemprocs..sp_autoformat reads table master..systypes (1) reads table master..syscolumns (1) reads table tempdb..systypes (1) reads table master..sysdevices (1) |
