Phil Streiff Blog

Better, faster, cheaper …pick two.

Database Inventory - Part II

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.  


(SELECT 'file_type' =      CASE          WHEN sysaltfiles.groupid <> THEN 'data'        WHEN sysaltfiles.groupid THEN 'log'    ENDAS 'file_type' , AS 'db_name' , AS 'logical_file_name' ,  sysaltfiles.filename AS 'physical_file_name' ,  (sysaltfiles.size 1024AS '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

Hope this helps!