Skip to content

Instantly share code, notes, and snippets.

@seancoyne

seancoyne/gist:14c86e40192d7eb5b00ff136401e37bd

Created August 4, 2020 17:05
Show Gist options
  • Save seancoyne/14c86e40192d7eb5b00ff136401e37bd to your computer and use it in GitHub Desktop.
Save seancoyne/14c86e40192d7eb5b00ff136401e37bd to your computer and use it in GitHub Desktop.
Download ZIP
sql server database size
Raw
gistfile1.txt
SELECT
@@SERVERNAME as InstanceName
, DB_NAME() as DatabaseName
, ISNULL(s.name+'.'+t.NAME, '**TOTAL**') AS TableName
, SUM(p.rows) AS RowCounts
--, SUM(a.total_pages) * 8 AS TotalSpaceKB
, SUM(a.total_pages) * 8/1024.0 AS TotalSpaceMB
, SUM(a.total_pages) * 8/1024.0/1024.0 AS TotalSpaceGB
, SUM(a.used_pages) * 8/1024.0 AS UsedSpaceMB
, (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024.0 AS UnusedSpaceMB
FROM sys.tables t
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i
ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p
ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%' -- filter out system tables for diagramming
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY s.name+'.'+t.Name
WITH ROLLUP
ORDER BY TotalSpaceMB DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment