Skip to content

Instantly share code, notes, and snippets.

@Cyberloki

Cyberloki/Find Large MSSQL Database tables by tablesize.sql

Created September 22, 2013 21:46
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Cyberloki/6664191 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Cyberloki/6664191 to your computer and use it in GitHub Desktop.
Download ZIP
Find Large MSSQL Database tables by tablesize
Raw
Find Large MSSQL Database tables by tablesize.sql
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
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%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment