SQL Server 查詢Table & Index的Size

查詢Table size
SELECT 
    t.NAME AS TableName,p.rows AS RowCounts,
    SUM(a.total_pages)*8/1024 AS TotalSpaceMB, 
    SUM(a.used_pages)*8/1024 AS UsedMB, 
    (SUM(a.total_pages) - SUM(a.used_pages))*8/1024 AS UnusedMB
FROM       sys.tables t
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 1=1
    --and t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    --AND i.OBJECT_ID > 255 
GROUP BY t.Name, p.Rows
ORDER BY 3 desc

查詢Index size
SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
       OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
       I.NAME AS IndexName,SUM(a.used_pages)*8 AS 'Indexsize(KB)'
FROM sys.indexes I
JOIN sys.partitions AS p ON p.OBJECT_ID = I.OBJECT_ID AND p.index_id = I.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
-- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1
GROUP BY I.OBJECT_ID,I.index_id,I.name
ORDER BY SchemaName, ObjectName, IndexName