SET NOCOUNT ON
/*
* Update usage statistics. Not a necessary step
* but will provide more accurate results
*/
DBCC UPDATEUSAGE(0)
DECLARE @database_id_start INT
SET @database_id_start = 0
/* Uncomment the line below to exclude master, model, msdb, temp */
SET @database_id_start = 4
CREATE TABLE #files (
[database_id] [smallint] NOT NULL,
[size_MB] [float] NULL,
[used_mb] [float] NULL,
[free_mb] [float] NULL
)
-- SQL 2000
IF (SELECT LEFT(CAST(SERVERPROPERTY('productversion') AS NVARCHAR(128)) , 1)) = '8'
BEGIN
EXEC sp_msForEachDB 'Use [?]; INSERT INTO #files SELECT
DB_ID()
,CAST(ROUND(([size]*8.0)/(1024),2) AS FLOAT) AS [size_MB]
,CAST(ROUND((FILEPROPERTY([name],''SpaceUsed'')*8.0)/(1024),2) AS FLOAT) AS [used_mb]
,CAST(ROUND((([size]-FILEPROPERTY([name],''SpaceUsed''))*8.0)/(1024),2) AS FLOAT) AS [free_mb]
FROM dbo.sysfiles df'
END
-- SQL 2005+
ELSE
BEGIN
EXEC sp_msForEachDB 'Use [?]; INSERT INTO #files SELECT
DB_ID()
,CAST(ROUND(([size]*8.0)/(1024),2) AS FLOAT) AS [size_MB]
,CAST(ROUND((FILEPROPERTY([name],''SpaceUsed'')*8.0)/(1024),2) AS FLOAT) AS [used_mb]
,CAST(ROUND((([size]-FILEPROPERTY([name],''SpaceUsed''))*8.0)/(1024),2) AS FLOAT) AS [free_mb]
FROM sys.database_files df'
END
/* Show individual databse sizes */
SELECT @@SERVERNAME AS [Server Name], DB_NAME([database_id]) AS [Database Name],
CAST(SUM([size_MB]/1024) AS NUMERIC(12,3)) AS [Size (in GB)],
CAST(SUM([used_MB]/1024) AS NUMERIC(12,3)) AS [Used (in GB)],
CAST(SUM([free_MB]/1024) AS NUMERIC(12,3)) AS [Free (in GB)]
FROM #files
WHERE database_id > @database_id_start
GROUP BY [database_id] ORDER BY [Database Name]
/* Show total for all databases on instance */
SELECT CAST(SUM([size_MB]/1024) AS NUMERIC(12,3)) AS [Total Size (in GB)],
CAST(SUM([used_MB]/1024) AS NUMERIC(12,3)) AS [Total Used (in GB)],
CAST(SUM([free_MB]/1024) AS NUMERIC(12,3)) AS [Total Free (in GB)]
FROM #files
WHERE database_id > @database_id_start
DROP TABLE #files