Printable View
I`m familiar with the sp_help_revdatabase stored proc which gives info on which devices a database is stored on. But I thought I`d heard of another sproc (or even a query of certain system tables) which could be used to determine the order of device creation for an entire SQL Server. The sysdevices table is fine, but doesn`t show the virtual device number, or whatever number indicates which devices were created first, second, etc.
Any suggestions?
Try sp_helpdevice. This stored procedure displays a list of all available devices as well as
information about databases devices and dump devices. It can be used to determine the next
available virtual device number.
Hope this answers your question
:-)
Rome
8/27/98 7:11:55 AM, wrote:
> I`m familiar with the sp_help_revdatabase stored proc which gives info on
> which devices a database is stored on. But I thought I`d heard of another
> sproc (or even a query of certain system tables) which could be used to
> determine the order of device creation for an entire SQL Server. The
> sysdevices table is fine, but doesn`t show the virtual device number, or
> whatever number indicates which devices were created first, second,
> etc.
Any suggestions?
If you look into sp_helpdevice you`ll see how they(Microsoft)
determine VDNumber:
select device_name = d.name, physical_name = substring(d.phyname, 1, 46),
d.status, d.cntrltype,
device_number = convert(tinyint, substring(convert(binary(4), d.low),v.low, 1)),
d.low, d.high
from sysdevices d, master.dbo.spt_values v
where v.type = `E`
and v.number = 3
OR
simply run this:
select name, low/16777216 VD_Number
from sysdevices
where status in (2,3)