Database Data & Log File Location

Whenever I take on a new project, I always have to go in and do a little digging, to find all of the data and log files (among other things).  I rarely use the default C:\Program Files\ location for my database files, but you know... there are many different ways to get it done.

Use this to return the default data and log file directories for an instance, regardless of  existing databases.  

 DECLARE @SmoDefaultFile NVARCHAR(1000)
 EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE',  
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', 
 @SmoDefaultFile OUTPUT


 DECLARE @SmoDefaultLog NVARCHAR(1000)
 EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', 
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', 
 @SmoDefaultLog OUTPUT

 SELECT 
    ISNULL(@SmoDefaultFile,N'') [Default MDF Location],
    ISNULL(@SmoDefaultLog,N'') [Default LDF Location]

Use this to return details specific to the the databases on the server, using sp_msforeachdb with a select against sys.database_files:

  IF OBJECT_ID('DatabaseFiles') IS NULL
  BEGIN
    SELECT TOP 0 * INTO DatabaseFiles
    FROM sys.database_files


    ALTER TABLE DatabaseFiles
    ADD CreationDate DATETIME DEFAULT(GETDATE())
  END


  EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, 
  GETDATE() FROM [?].sys.database_files'


  SELECT * FROM dbo.DatabaseFiles

And don't forget this one, if you want to return your database file sizes back in MB, rather than KB:   Database File Size (MB)