April 27, 2012

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

No comments:

Post a Comment

Total Pageviews