sql-server disk usage by Each table or view (query)

علی ذوالفقار
1402/12/21 10:17:25 (148)
SELECT * FROM 
(SELECT 
    T.NAME AS TABLE_NAME,
    S.NAME AS SCHEMA_NAME,
    P.ROWS AS ROW_COUNT,
    SUM(A.TOTAL_PAGES) * 8 AS TOTAL_SPACE_KB, 
		SUM(A.TOTAL_PAGES) * 8 /1000000 AS TOTAL_SPACE_GB, 
    SUM(A.USED_PAGES) * 8 AS USED_SPACE_KB, 
    (SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)) * 8 AS UNUSED_SPACE_KB
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
LEFT OUTER JOIN 
    SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
WHERE 
    T.NAME NOT LIKE 'DT%' 
    AND T.IS_MS_SHIPPED = 0
    AND I.OBJECT_ID > 255 
GROUP BY 
    T.NAME, S.NAME, P.ROWS
) TBL 
ORDER BY TOTAL_SPACE_KB DESC      
Back