Monday, July 12, 2010

Get all database sizes on server

DECLARE @DBSize TABLE([DatabaseName] [nvarchar](75) NOT NULL,
      [Size] [decimal] NOT NULL,
      [Name] [nvarchar](75) NOT NULL,
      [Filename] [nvarchar](90) NOT NULL);
DECLARE @FixedDrive TABLE ([Drive] [char](1) NOT NULL,
      [MBFree] [decimal] NOT NULL);

INSERT INTO @DBSize
EXEC sp_MSforeachdb 'SELECT ''?'' as DatabaseName, 
      CASE WHEN [?]..sysfiles.size * 8 / 1024 = 0 THEN 1 
       ELSE [?]..sysfiles.size * 8 / 1024 END AS size, 
      [?]..sysfiles.name, 
      [?]..sysfiles.filename 
     FROM [?]..sysfiles';

INSERT INTO @FixedDrive
EXEC xp_fixeddrives;

SELECT RTRIM(CAST(DatabaseName AS VARCHAR(75))) DatabaseName,
        Drive,Filename, Cast(Size AS INT) Size,
        Cast(MBFree AS VARCHAR(10)) MB_Free
FROM @DBSize t1
 INNER JOIN @FixedDrive t2 ON LEFT(t1.Filename, 1) = t2.Drive
GROUP BY DatabaseName, Drive, MBFree, Filename, Cast(Size AS INT)
ORDER BY Drive, Size DESC;

SELECT Drive [Total Space Used],
        Cast(Sum(Size) AS VARCHAR(10)) [Total Size],
        Cast(MBFree AS VARCHAR(10)) [MB Free]
FROM @DBSize t1
        INNER JOIN @FixedDrive t2 ON LEFT(t1.Filename, 1) = t2.Drive
GROUP BY Drive, MBFree;

No comments:

Post a Comment