USE AdventureWorks2012;SELECT * , Q.page_count * 8 / 1024 AS MBFROM ( SELECT S2.[name] AS schema_name , T.[name] AS table_name , SUM(S.row_count) AS row_count , SUM(S.used_page_count) AS page_count , CASE SUM(S.used_page_count) WHEN 0 THEN 0 ELSE SUM(S.row_count) / SUM(S.used_page_count) END AS rows_per_page FROM sys.dm_db_partition_stats S INNER JOIN sys.tables T ON T.object_id = S.object_id INNER JOIN sys.schemas S2 ON S2.schema_id = T.schema_id WHERE ( S.index_id = 0 OR S.index_id = 1 ) GROUP BY S2.[name] , T.[name] ) QORDER BY Q.page_count DESC;
Open in new window
Works on 2005+.