SQL SERVER – List Tables with Size and Row Counts

What defines a table as a huge table, is it row count or size of the table? Well, during Comprehensive Database Performance Health Check I often want to know both the database which I am tuning. Today we will see a very simple script that lists table names with the size of the table and along with that row counts.

Run the following script in your SSMS.

SELECT
t.NAME AS TableName,
SUM(p.rows) AS RowCounts,
(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 i.OBJECT_ID > 255
AND i.index_id IN (0,1)
GROUP BY t.NAME
ORDER BY TotalSpaceMB DESC

I hope you find this short but effective script to find Size and Row Counts helpful. Here are few other blog posts which you may find interesting.

Here are my few recent videos and I would like to know what is your feedback about them. You can subscribe to my youtube channel here.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version