Friday, October 31, 2008

Finding size of Table/Indexes in SQL Server 2005



Original file

http://www.extremeexperts.com/sql/Scripts/FindSizeOfTable.aspx

Updated query

WITH table_space_usage
( schema_name, table_name, index_name, used, reserved, ind_rows, tbl_rows )
AS (
SELECT s.Name
, o.Name
, COALESCE(i.Name, 'HEAP')
,p.used_page_count -- total_used_pages
,p.reserved_page_count * 8 --total_reserved_pages
,p.row_count --rows
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o
ON o.object_id = p.object_id
INNER JOIN sys.schemas as s
ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i
on i.object_id = p.object_id and i.index_id = p.index_id
WHERE o.type_desc = 'USER_TABLE'
and o.is_ms_shipped = 0
)
SELECT t.schema_name
, t.table_name
, t.index_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
, case grouping(t.index_name)
when 0 then sum(t.ind_rows)
else sum(t.tbl_rows) end as rows
FROM table_space_usage as t
GROUP BY
t.schema_name
, t.table_name
, t.index_name
WITH ROLLUP
ORDER BY
grouping(t.schema_name)
, t.schema_name
, grouping(t.table_name)
, t.table_name
, grouping(t.index_name)
, t.index_name

No comments: