sp_helpdevice (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpdevice(nvarchar @devname)

MetaData:

 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  
)  
  
--  alter the columns to master's collation, since we are inserting names from master.dbo.sysdevices.  
--  This is needed because if this proc is being run in CDB, then the columns would be the CDB's data   
--  collation that could be different from master db's collation.  
--    
declare @alterTab nvarchar(1024)  
select @alterTab = N'alter table #spdevtab alter column name sysname COLLATE ' + convert(nvarchar(256), SERVERPROPERTY('collation')) + ' NOT NULL'  
exec(@alterTab)  
select @alterTab = N'alter table #spdevtab alter column statusdesc nvarchar(255) COLLATE ' + convert(nvarchar(256), SERVERPROPERTY('collation')) + ' NULL'  
exec(@alterTab)  
  
  
--   
--   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