If you want to get a statistics on a number of database pages used by a specific table in SQL Server, you can query DMV sys.dm_db_partition_stats.
The following query will show you the number of pages per table and each heap/clustered/nonclustered index.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT OBJECT_SCHEMA_NAME(s.object_id) AS [Schema name], OBJECT_NAME(s.object_id) AS [Table name], s.index_id AS [Index id], CASE s.index_id WHEN 0 THEN N'Heap' WHEN 1 THEN N'Clustered index' ELSE N'Nonclustered index' END AS [Index type], s.used_page_count AS [Total pages], s.row_count AS [Total rows], CASE s.row_count WHEN 0 THEN NULL ELSE 8192 * s.used_page_count / s.row_count END AS [Bytes per row] FROM sys.dm_db_partition_stats s ORDER BY [Schema name], [Table name], s.index_id; |