Please use FireFox to view this page

create procedure 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 (@devname is null or name = @devname)) begin raiserror(15012,-1,-1,@devname) return (1) 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'pipe' from master.dbo.sysdevices d, #spdevtab where d.cntrltype = 6 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 8 is a skip tape header. */ 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 = 8 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