Any way to quickly tell which database, if any, is attached to a .mdf file?

sql sql-server sql-server-2005 sql-server-2008 mdf
Jeremy Wiggins picture Jeremy Wiggins · May 19, 2011 · Viewed 10k times · Source

Assume SQL Server 2005 / 2008 with a large number of databases. Is there any way to quickly tell which database, if any, is attached to a particular .mdf file?

We've dropped some databases over time and would like to clean up some lingering .mdf's to clear up space on the server. Currently the only way I know of is to look at the properties of each database, one by one, in Management Studio and make a list of the files they're attached to. Looking for something a little more efficient than this, if anything exists.

Answer

gbn picture gbn · May 19, 2011

sys.master_files contains one row per database for the first file (id = 1) for that database. That is, system tables will always be in fileid = 1 for each database

This is all you need:

SELECT
   DB_NAME(database_id), physical_name
FROM
   sys.master_files

Related questions

How to avoid the "divide by zero" error in SQL?

I have this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: …

Read More
sql query to return differences between two tables

I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the …

Read More
Simple DateTime sql query

How do I query DateTime database field within a certain range? I am using SQL SERVER 2005 Error code below SELECT * FROM TABLENAME WHERE DateTime >= 12/04/2011 12:00:00 AM AND DateTime <= 25/05/2011 3:53:04 AM Note that I need to get rows within a certain …

Read More