SQL Server Discovery

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

February 2007 Blog Posts

Gather a Database Inventory

As a DBA for a large corporation, I manage literally hundreds of databases. In order to help keep track of all the databases and log files, I needed a way to quickly gather an inventory of all databases located on each sql named instance, so I came up with this simple little script that also includes a filesize in megabytes column:  USE [Master] GO SELECT sysdatabases.name AS 'db_name' , (select 'file_type' = CASE WHEN sysaltfiles.groupid = 1 THEN 'data' WHEN sysaltfiles.groupid = 0 THEN 'log' END) as 'file_type' , sysaltfiles.name AS '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...

posted @ Wednesday, February 28, 2007 9:40 AM | Feedback (7) |

Powered by:
Powered By Subtext Powered By ASP.NET