Sometimes you need to know the size of your SQL Server databases (to monitor how large they are growing, managerial reports etc). Here is a quick little script you can run to help you on your way to finding out …

USE master;

-- Retrieve how large a size of a page is
DECLARE @PageSize varchar(10)
SELECT @PageSize=v.low/1024
FROM master..spt_values v
WHERE v.number=1 and v.type='E'

-- Create temporary table listing all application databases
SELECT [name] AS DatabaseName, convert(float, NULL) AS [Size]
INTO #temp
FROM dbo.sysdatabases WHERE dbid > 4

-- Loop through the databases and update the temporary table
DECLARE @SQL varchar (8000)
SET @SQL=''
WHILE EXISTS (SELECT DatabaseName FROM #temp WHERE [Size] IS NULL)
BEGIN
    -- Note: size in sysfiles is in number of pages, so multiply it by size of a page
    SELECT @SQL='UPDATE #temp SET [Size]=(SELECT ROUND(SUM([size])*'+@PageSize+'/1024,0) FROM '+quotename(databasename)+'.dbo.sysfiles)
                                                                                         WHERE DatabaseName=''' + DatabaseName + ''''
    FROM #temp
    WHERE [Size] IS NULL
    EXEC (@SQL)
END

-- Select the report info
SELECT @@ServerName AS Instance,
       DatabaseName,
       LTRIM(RIGHT(LEFT(@@VERSION,37),10)) AS Version,
       convert(varchar, ROUND((Size/1024),4)) AS GB
FROM #temp
ORDER BY DatabaseName

-- Clean up
DROP TABLE #temp

Hope this helps,

Stefan.

For more Web Code, ASP.NET, SQL Server and other development tips, please check back here at webcodeblog.com often.