SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, comments - 80, trackbacks - 0

November 2007 Blog Posts

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.   USE [Master]GOSELECT   (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! 

posted @ Tuesday, November 27, 2007 2:08 PM | Feedback (0) |

Powered by:
Powered By Subtext Powered By ASP.NET