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

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开发者_如何学JAVA 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.

---

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

---

This may help.

declare @files table (
    db_name sysname,
    physical_name nvarchar(260)
)

insert into @files
    exec sp_MSforeachdb 'select "?", physical_name from ?.sys.database_files'

select db_name, physical_name 
    from @files

---

select db_name(database_id), * from sys.master_files

Will list all the files of all the databases known on the system.

---

You could also use OrcaMDF for this:

using (var file = new MdfFile(@"C:\Database.mdf"))
{
    var bootPage = file.GetBootPage();
    Console.WriteLine(bootPage.DatabaseName);
}

This'll allow you to query the mdf's for their database name without attaching them to the database server. Note that this should be done on the primary data file, in case there are multiple files. Disclaimer - I'm the author of OrcaMDF.

Looping through all files in the data directory, it'd be easy to join that with sys.databases and see which ones don't match up, and are hence non-attached mdf files.

Edit: Posted a more thorough example on my blog: http://improve.dk/archive/2011/05/19/checking-which-database-is-stored-in-a-deattached-mdf-file.aspx

---

Low tech solution... move the mdf file to another location. If it is attached, SQL server would not let you move it :)

From command prompt

cd X:\TheDir\Where\MDF\File\Are
mkdir UnusedMdf
move *.mdf UnusedDBFiles
move *.ldf UnusedDBFiles

All the unused files would be moved to UnusedDBFiles.

---

private bool IsDbAttached()
        {
            const string isAttachedSqL = @"SELECT count(*)
                                        FROM sys.master_files
                                        WHERE  DB_NAME(database_id) = @DbName";

            bool isAttached = false;
            try
            {
                using (var connection = new SqlConnection(this.connectionString))
                using (var command = new SqlCommand(isAttachedSqL, connection))
                {
                    command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = "dbName";
                    connection.Open();
                    var count = command.ExecuteScalar();
                    isAttached = (int)count > 0;
                }
            }
            catch
            {
                throw;
            }

            return isAttached;

        }