Menu

Want to work with us? We're on the lookout for a talented Digital Project Manager.

We're hiring

SQL Server Code to Find Size of Tables Within a Database and ORDER BY table size

Web Bureau


22 September 2011 by Web Bureau

Found this script which allows you to order by table size http://www.sqlservercurry.com/2011/02/sql-server-count-rows-in-tables-and-its.html

 

-- Count All Rows and Size of Table by SQLServerCurry.com

SELECT

TableName = obj.name,

TotalRows = prt.rows,

[SpaceUsed(KB)] = SUM(alloc.used_pages)*8

FROM sys.objects obj

JOIN sys.indexes idx on obj.object_id = idx.object_id

JOIN sys.partitions prt on obj.object_id = prt.object_id

JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id

WHERE

obj.type = 'U' AND idx.index_id IN (0, 1)

GROUP BY obj.name, prt.rows

ORDER BY [SpaceUsed(KB)] desc 

Grow your businessStart a project with us today.