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)