View physical file path of all Databases

I recently stumbled across a .mdf and .ldf file for a database while trying to clear up space on a work machine.

Although the database did have a name, I couldn’t find a database attached to the SQL Server instance that matched that name.

So, rather than look at the properties of each attached database to view the file details, I wanted a way to query all of the attached databases.

Enter: the internet. Thanks to SQL Autority I have the script below.

  name, physical_name AS current_file_location

This will list out the location of the database file. Fantastic!

As it turns out, I was looking at a SQL Server 2012 instance, but there was also a SQL Server 2014 instance on the machine and the database in question was attached to the 2014 instance. Hence why I couldn’t find it. D’oh.


One comment

  1. John Edward · March 7

    Great info!