Largest tables on an instance

Download PDF

Here is a quick script that I find myself using frequently to find the largest tables on an entire SQL Server instance.

DROP TABLE ##tableSizes;
CREATE TABLE ##tableSizes
(
TableName VARCHAR(1000),
EstimatedRowCount BIGINT,
TotalSpaceGB DECIMAL(10,2)
);

EXEC sp_MSforeachdb 'USE [?];

INSERT INTO ##tableSizes
SELECT ''['' + DB_NAME() + ''].['' + s.Name + ''].['' + t.NAME + '']'' AS TableName,
p.rows AS EstimatedRowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) / 1024 AS NUMERIC(36, 2)) AS TotalSpaceGB
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
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ''dt%''
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
';

SELECT @@SERVERNAME, * FROM ##tableSizes
WHERE TotalSpaceGB > 10
ORDER BY TotalSpaceGB DESC

 

More from Stedman Solutions:

SteveStedman5
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!

Leave a Reply

Your email address will not be published. Required fields are marked *

*