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.

SQL SERVER - List Tables with Size and Row Counts rowcounts-800x291

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)

, ,
Previous Post
MySQL MariaDB – Query Using Temp Table
Next Post
Forwarded Records and Performance – SQL in Sixty Seconds #155

Related Posts

Leave a Reply

Menu