Archive for the tag 'dynamic management views'

Table Size Query

Posted by Clay Lenhart on March 8, 2008 under SQL Server Administration

The following query lists the tables and the space they use. This query is much faster (sub-second) than a standard SELECT COUNT(*) query since it uses the dynamic management views in SQL Server rather than scanning your data.

SELECT sum ( used_page_count ) * 8 AS SizeKB,
  sum(row_count) AS [RowCount], object_name ( object_id ) AS TableName
FROM sys.dm_db_partition_stats
WHERE index_id=0 OR index_id=1
GROUP BY object_id
ORDER BY sum ( used_page_count ) DESC;

Tags: ,