Phil Streiff Blog

I worked on my Database Inventory script a bit and am releasing an enhanced version of the original.

When run in the Master database, the script displays a list of all user databases located in the specified sql named instance, along with file name, file type, file path and file size columns.  

USE [Master]
GO

SELECT
   (SELECT 'file_type' =  
   CASE  
       WHEN sysaltfiles.groupid <> 0 THEN 'data'
       WHEN sysaltfiles.groupid = 0 THEN 'log'
   END) AS 'file_type'
,  sysdatabases.name AS 'db_name'
,  sysaltfiles.name AS 'logical_file_name'
,  sysaltfiles.filename AS 'physical_file_name'
,  (sysaltfiles.size * 8 / 1024) AS 'file_size(MB)' -- file size in MB
FROM 
   dbo.sysdatabases
JOIN
   dbo.sysaltfiles ON
   (dbo.sysdatabases.dbid=dbo.sysaltfiles.dbid)
WHERE
   sysdatabases.dbid NOT IN ('1','2','3','4')
ORDER BY
   dbo.sysdatabases.name

Hope this helps!