Skip to content

Instantly share code, notes, and snippets.

@dannylloyd
Created April 7, 2015 12:43
Show Gist options
  • Select an option

  • Save dannylloyd/f2dc2e4e9a47c3658d51 to your computer and use it in GitHub Desktop.

Select an option

Save dannylloyd/f2dc2e4e9a47c3658d51 to your computer and use it in GitHub Desktop.
Displays tables with most rows in database
--Copied from here http://stackoverflow.com/questions/2094436/how-to-find-largest-objects-in-a-sql-server-database
SELECT t.NAME AS TableName,
i.name AS indexName,
SUM(p.rows) AS RowCounts,
SUM(a.total_pages) AS TotalPages,
SUM(a.used_pages) AS UsedPages,
SUM(a.data_pages) AS DataPages,
(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
(SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
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 i.OBJECT_ID > 255
AND i.index_id <= 1
GROUP BY t.NAME,
i.object_id,
i.index_id,
i.name
ORDER BY SUM(a.total_pages) DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment