If you need to find out which devices contain fragments of a database and their physical location, you can use the following SELECT statement:
select sda.name, sde.name, sde.phyname from master..sysdatabases sda, master..sysdevices sde, master..sysusages su where su.dbid=sda.dbid and su.vdevno=sde.vdevno and sda.name = @dbname
where @dbname is the name of you database.
This returns 3 columns:
- The name of the database
- The name of the device
- The physical location of the device
I use this when dropping proxy databases I’m creating for some temporary checks. I want to drop the database and drop their related devices and delete the files.
I have called all my proxy databases tempdb_old1, tempdb_old2,… So I’d get the information I require for my cleanup activity like this:
1> select sda.name, sde.name, sde.phyname from master..sysdatabases sda, master..sysdevices sde, master..sysusages su where su.dbid=sda.dbid and su.vdevno=sde.vdevno and sda.name like 'tempdb_old%' 2> go name name phyname ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------- tempdb_old1 tempdb_proxy_dev1 /db/proxy_tempdb1.dat tempdb_old2 tempdb_proxy_dev2 /db/proxy_tempdb2.dat tempdb_old3 tempdb_proxy_dev3 /db/proxy_tempdb3.dat tempdb_old4 tempdb_proxy_dev4 /db/proxy_tempdb4.dat (4 rows affected)