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