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, MAX(p.rows) AS RowCounts, (SUM(a.total_pages) * 8) / 1024.0 as TotalSpaceMB, (SUM(a.used_pages) * 8) / 1024.0 as UsedSpaceMB, (SUM(a.data_pages) * 8) /1024.0 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.
- Zoom in SSMS – SQL in Sixty Seconds #153
- Transfer Schema of Table – SQL in Sixty Seconds #152
- Find a Table in Execution Plan – SQL in Sixty Seconds #151
- Transactions and Variables – SQL in Sixty Seconds #150
- Count Table in Cache – SQL in Sixty Seconds #149
- List All Sessions – SQL in Sixty Seconds #148
- Line Numbers for SSMS Efficiency – SQL in Sixty Seconds #147
- Slow Running Query – SQL in Sixty Seconds #146
- Change Database and Table Collation – SQL in Sixty Seconds #145
- Infinite Loop – SQL in Sixty Seconds #144
- Efficiency Trick – Query Shortcut – SQL in Sixty Seconds #143
- SQL SERVER – 16 CPU vs 1 CPU : Performance Comparison – SQL in Sixty Seconds #142
- SQL SERVER – TOP and DISTINCT – Epic Confusion – SQL in Sixty Seconds #141
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
There is a minor rounding bug in above script due to integer division:
SELECT
t.NAME AS TableName,
SUM(p.rows) AS RowCounts,
(SUM(a.total_pages) * 8) / 1024.0 as TotalSpaceMB,
(SUM(a.used_pages) * 8) / 1024.0 as UsedSpaceMB,
(SUM(a.data_pages) * 8) /1024.0 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
should fix it. In my case I was seeing several tables with row counts > 0 but a 0 space utilization.
Thanks Jason, I reflected your changes in the script and will write a separate blog post giving credit to you.
The counted rows in this script are wrong when you have a table with multiple partitions.
To give you the correct value for rows in a table, please use “MAX(P.[rows])” instead of “SUM(P.[rows])”.
Fair point. Let me correct it.