SQL SERVER – List Tables with Size and Row Counts – Part 2

Earlier I had written a blog post SQL SERVER – List Tables with Size and Row Counts. Jason Horner, an SQL Server Expert, left a comment to the blog post and improved my earlier query. Thanks, Jason for fixing the rounding bug in the script for integer division.

SQL SERVER - List Tables with Size and Row Counts - Part 2 SizeandRow-800x170

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

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. If you have any other questions, do let me know by leaving a comment on the blog post. As you can see I read everything and do my best to reply.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – Available Free Space in Data and Log File
Next Post
SQL SERVER – Trigger on Database to Prevent Table Creation

Related Posts

2 Comments. Leave new

  • Good one. Few changes, so that the result matches to sp_spaceused which is being used from many years.

    SELECT t.NAME AS TableName, SUM(p.rows) AS RowCounts,
    (SUM(a.total_pages) * 8) / 1024.0 as ReservedSizeMB,
    (SUM(a.data_pages) * 8) /1024.0 as DataSizeMB,
    ((SUM(a.used_pages) * 8)-(SUM(a.data_pages) * 8)) / 1024.0 as IndexSizeMB,
    ((SUM(a.total_pages) * 8)-(SUM(a.used_pages) * 8)) / 1024.0 as UnUsedSizeMB
    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 ReservedSizeMB DESC
    go

    Reply

Leave a Reply

Menu