SQL Server Discovery

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

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]
GO

SELECT
   
(SELECT 'file_type' =      CASE          WHEN sysaltfiles.groupid <> THEN 'data'        WHEN sysaltfiles.groupid THEN 'log'    ENDAS 'file_type' ,  sysdatabases.name AS 'db_name' ,  sysaltfiles.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    dbo.sysdatabases.name

Hope this helps! 

Print | posted on Tuesday, November 27, 2007 2:08 PM |

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET