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