Program - Get size of all tables in database by SQL Server

計算硬體設備容量大小的時候

需要評估資料庫大概所佔的容量

粗略使用語法查看資訊

SELECT 

    T.NAME AS TABLENAME,

    P.ROWS AS ROWCOUNTS,

    SUM(A.TOTAL_PAGES) * 8 /1024 AS TOTALSPACEMB, 

    SUM(A.USED_PAGES) * 8 /1024 AS USEDSPACEMB, 

    (SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)) * 8 /1024 AS UNUSEDSPACEMB

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

ORDER BY 

    T.NAME