Determine file path of database

Question

Hi to all,

Is it possible to run a script that gives the directory and files for all databases installed on a server?

So for example, instead of launching SQL server management studio and checking properties tab for the database and then checking the database files for the data and log file and then doing this for all databases which can be a bit tedious and also assumes we have studio installed.

Thanks JD.

Answer 1

SELECT DB_NAME(database_id), type_desc, physical_name FROM sys.master_files

Other info is avaiable from sys.master_files, like logical name etc

Answer 2

SELECT 
    name AS 'File Name', 
    physical_name AS 'Physical Name', 
    size/128 AS 'Total Size(MB)',
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 
        'Available Space(MB)'
FROM 
    sys.database_files;