T-SQL:
;With CTE as
(
SELECT
t.NAME AS 'Table Name',
i.type_desc as 'Index Type',
CASE WHEN i.name IS NULL THEN 'NULL (HEAP)' ELSE i.name END as 'Index Name',
p.rows AS 'Row Count',
SUM(a.total_pages) * 8 AS 'TotalSpace(KB)',
SUM(a.used_pages) * 8 AS 'UsedSpace(KB)',
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 'UnusedSpace(KB)',
ROW_NUMBER() OVER(PARTITION BY t.name ORDER BY (SUM(a.total_pages) * 8) DESC) AS 'RowNumber'
FROM
sys.tables t
INNER JOIN
sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.is_ms_shipped = 0 --filter out internal tables
AND t.NAME NOT LIKE 'dt%' --filter out system tables for diagramming
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, i.type_desc, i.name, p.rows
)
Select CTE.[Table Name], CTE.[Index Type], CTE.[Index Name], CTE.[Row Count], CTE.[TotalSpace(KB)], CTE.[UsedSpace(KB)], CTE.[UnUsedSpace(KB)], t1.[TotalSpace(KB)] as 'TotalSpaceTable-Rollup(KB)', CTE.[RowNumber] from CTE
inner join
(Select
[Table Name], SUM([TotalSpace(KB)]) as 'TotalSpace(KB)'
from CTE
group by CTE.[Table Name]
) t1
on CTE.[Table Name] = t1.[Table Name]
order by 'TotalSpaceTable-Rollup(KB)' DESC, CTE.[Table Name] ASC, CTE.[TotalSpace(KB)] DESC
|
|
|
|
|
|