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)
