create procedure sys.sp_helpdevice --- 1996/04/08 00:00
@devname sysname = NULL /* device to check out */
as
/* Create temp tables before any DML to ensure dynamic
** Create a temporary table where we can build up a translation of
** the device status bits.
*/
create table #spdevtab
(
name sysname NOT NULL,
statusdesc nvarchar(255) null
)
/*
** See if the device exists.
*/
if not exists (select * from master.dbo.sysdevices where name = @devname)
begin
if (@devname is not null)
begin
raiserror(15012,-1,-1,@devname)
return (1)
end
end
set nocount on
/*
** Initialize the temporary table with the names of the devices.
*/
insert into #spdevtab (name)
select name
from master.dbo.sysdevices
where (@devname is null or name = @devname)
/*
** Now figure out what kind of controller type it is.
**
** cntrltype = 0 special (data disk)
** 2 disk (dump)
** 3-4 floppy (dump) Not supported in SQL 7.0
** 5 tape No size information in SQL 7.0
** 6 pipe
** 7 virtual_device
*/
update #spdevtab
set statusdesc = N'special'
from master.dbo.sysdevices d, #spdevtab
where d.cntrltype = 0
and #spdevtab.name = d.name
update #spdevtab
set statusdesc = N'disk'
from master.dbo.sysdevices d, #spdevtab
where d.cntrltype = 2
and #spdevtab.name = d.name
update #spdevtab
set statusdesc = N'tape'
from master.dbo.sysdevices d, #spdevtab
where d.cntrltype = 5
and #spdevtab.name = d.name
update #spdevtab
set statusdesc = N'virtual_device'
from master.dbo.sysdevices d, #spdevtab
where d.cntrltype = 7
and #spdevtab.name = d.name
update #spdevtab
set statusdesc = N'UNKNOWN DEVICE'
from master.dbo.sysdevices d, #spdevtab
where d.cntrltype >= 8
and #spdevtab.name = d.name
/*
** Now check out the status bits and turn them into english.
** Status of 16 is a dump device.
*/
update #spdevtab set statusdesc = statusdesc + N', ' + rtrim(v.name)
from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
where v.type = 'V' and v.number > -1
and d.status & v.number = 16
and #spdevtab.name = d.name
/*
** Status of 1 is a default disk.
*/
update #spdevtab set statusdesc = statusdesc + N', ' + rtrim(v.name)
from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
where v.type = 'V' and v.number > -1
and d.status & v.number = 1
and #spdevtab.name = d.name
/*
** Status of 2 is a physical disk.
*/
update #spdevtab
set statusdesc = substring(statusdesc, 1, 225) + N', ' + rtrim(v.name)
from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
where v.type = 'V' and v.number > -1
and d.status & v.number = 2
and #spdevtab.name = d.name
/*
** Add in its size in MB.
*/
update #spdevtab
set statusdesc = statusdesc + N', ' + convert(varchar(10),
round((convert(float, d.size) * (select low from master.dbo.spt_values
where type = 'E' and number = 1)
/ 1048576), 1)) + ' MB'
from master.dbo.sysdevices d, #spdevtab, master.dbo.spt_values v
where d.status & 2 = 2
and #spdevtab.name = d.name
and v.number = 1
and v.type = 'E'
/*
** Status of 4 is a logical disk.
*/
update #spdevtab
set statusdesc = substring(statusdesc, 1, 225) + N', ' + rtrim(v.name)
from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
where v.type = 'V' and v.number > -1
and d.status & v.number = 4
and #spdevtab.name = d.name
/*
** Status of 4096 is read only.
*/
update #spdevtab
set statusdesc = substring(statusdesc, 1, 225) + N', ' + rtrim(v.name)
from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
where v.type = 'V' and v.number > -1
and d.status & v.number = 4096
and #spdevtab.name = d.name
/*
** Status of 8192 is deferred.
*/
update #spdevtab
set statusdesc = substring(statusdesc, 1, 225) + N', ' + (v.name)
from master.dbo.sysdevices d, master.dbo.spt_values v, #spdevtab
where v.type = 'V' and v.number > -1
and d.status & v.number = 8192
and #spdevtab.name = d.name
set nocount off
/*
** The device number is in the high byte of sysdevices.low so
** spt_values tells us which byte to pick out.
*/
select device_name = d.name, physical_name = d.phyname,
description = #spdevtab.statusdesc,
status = d.status&12319, d.cntrltype,
size
from master.dbo.sysdevices d, #spdevtab, master.dbo.spt_values v
where d.name = #spdevtab.name
and v.type = 'E'
and v.number = 3
return(0) -- sp_helpdevice