Script for database sizes per server

I don’t remember where I got this script, anyways, it’s a nice add-on to you toolbox. The scripts lists up the size of your databases. This info is also available through SSMS, but the formatting is different.

Please note that the script will fail if your databases has a name that requires brackets. Adding them should be a easy exercise.

SET Nocount ON     CREATE TABLE ##helpfile     (       ObsvDate DATETIME NULL,       ServerName VARCHAR(50) NULL,       DbName VARCHAR(100) NULL,       FileLogicalName VARCHAR(100) NULL,       FileID INT NULL,       FileGroupID INT NULL,       FilePath VARCHAR(100) NULL,       FileGroupName VARCHAR(50) NULL,       FileTotalSizeKB VARCHAR(20) NULL,       FileMaxSizeSetting VARCHAR(20) NULL,       FileGrowthSetting VARCHAR(20) NULL,       FileUsage VARCHAR(20) NULL,       FileTotalSizeMB dec(19, 4) NULL,       FileUsedSpaceMB dec(19, 4) NULL,       FileFreeSpaceMB dec(19, 4) NULL,            )   CREATE TABLE ##filestats     (       DbName VARCHAR(100) NULL,       FileID INT NULL,       FileGroupID INT NULL,       FileTotalSizeMB dec(19, 4) NULL,       FileUsedSpaceMB dec(19, 4) NULL,       FileFreeSpaceMB dec(19, 4) NULL,       FileLogicalName VARCHAR(100) NULL,       FilePath VARCHAR(100) NULL     )   CREATE TABLE ##sqlperf     (       DbName VARCHAR(100) NULL,       LogFileSizeMB dec(19, 4) NULL,       LogFileSpaceUsedpct dec(19, 4) NULL,       Status INT NULL     )   INSERT  ##sqlperf         (           DbName,           LogFileSizeMB,           LogFileSpaceUsedpct,           Status         )         EXEC ( 'DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS '             )   EXEC sp_MSForeachDB        --@command1 = 'Use ?; DBCC UPDATEUSAGE(0)',     @command1 = 'Use ?;Insert ##helpfile (FileLogicalName, FileID, FilePath, FileGroupName, FileTotalSizeKB, FileMaxSizeSetting, FileGrowthSetting,FileUsage) Exec sp_helpfile; update ##helpfile set dbname = ''?'' where dbname is null',     @command2 = 'Use ?;Insert  ##filestats (FileID, FileGroupID, FileTotalSizeMB, FileUsedSpaceMB, FileLogicalName, FilePath) exec (''DBCC SHOWFILESTATS WITH NO_INFOMSGS ''); update ##filestats set dbname = ''?'' where dbname is null'   -- remove any db's that we don't care about monitoring DELETE  FROM ##filestats WHERE   CHARINDEX(dbname,                   'master-model-pubs-northwind-distribution-msdb') > 0 DELETE  FROM ##helpfile WHERE   CHARINDEX(dbname,                   'master-model-pubs-northwind-distribution-msdb') > 0 DELETE  FROM ##sqlperf WHERE   CHARINDEX(dbname,                   'master-model-pubs-northwind-distribution-msdb') > 0   UPDATE  ##filestats SET     FileTotalSizeMB = ROUND(FileTotalSizeMB * 64 / 1024,                                 2),         FileUsedSpaceMB = ROUND(FileUsedSpaceMB * 64 / 1024,                                 2) WHERE   FileFreeSpaceMB IS NULL   UPDATE  ##filestats SET     FileFreeSpaceMB = FileTotalSizeMB - FileUsedSpaceMB WHERE   FileFreeSpaceMB IS NULL   UPDATE  ##helpfile SET     FileGroupID = 0 WHERE   FileUsage = 'log only'   UPDATE  ##helpfile SET     FileGroupID = b.FileGroupID,         FileTotalSizeMB = b.FileTotalSizeMB,         FileUsedSpaceMB = b.FileUsedSpaceMB,         FileFreeSpaceMB = b.FileFreeSpaceMB FROM    ##helpfile a,         ##filestats b WHERE   a.FilePath = b.FilePath         AND a.FileUsage = 'data only'   UPDATE  ##helpfile SET     FileTotalSizeMB = ROUND(CAST(REPLACE(FileTotalSizeKB, ' KB', '') AS dec(19, 4))                                 / 1024, 2) WHERE   FileTotalSizeMB IS NULL   UPDATE  ##helpfile SET     FileUsedSpaceMB = ROUND(FileTotalSizeMB                                 * b.LogFileSpaceUsedpct                                 * 0.01, 2),         FileFreeSpaceMB = ROUND(FileTotalSizeMB * ( 100 - b.LogFileSpaceUsedpct )                                 * 0.01, 2) FROM    ##helpfile a,         ##sqlperf b WHERE   a.dbname = b.dbname         AND a.FileUsage = 'log only'   DECLARE @obsvdate DATETIME SET @obsvdate = GETDATE() UPDATE  ##helpfile SET     ObsvDate = @obsvdate WHERE   ObsvDate IS NULL     -- 97 : 122 = a to z -- 65 : 90  = A to Z UPDATE  ##helpfile SET     FilePath = STUFF(FilePath, 1, 1,                          UPPER(LEFT(FilePath, 1))) WHERE   UNICODE(LEFT(FilePath, 1)) BETWEEN 97 AND 122   UPDATE  ##helpfile SET     servername = @@servername WHERE   ServerName IS NULL   SELECT  ObsvDate,         ServerName,         DbName,         FileLogicalName,         FileID,         FilePath,         FileGroupID,         FileGroupName,         FileTotalSizeKB,         FileTotalSizeMB,         FileUsedSpaceMB,         FileFreeSpaceMB,         FileMaxSizeSetting,         FileGrowthSetting,         FileUsage FROM    ##helpfile ORDER BY FilePath   DROP TABLE ##helpfile DROP TABLE ##filestats DROP TABLE ##sqlperf     SET Nocount OFF