테이블/인덱스 사이즈 조회

• Users 테이블의 용량 정보 조회

• sys.tables + sys.schemas + sys.dm_db_partition_stats 조합

사용자별(스키마별) 테이블 + 테이블 사이즈

SELECT
    s.name AS schema_name,
    t.name AS table_name,
    SUM(ps.row_count) AS row_count,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 AS DECIMAL(18,2)) AS reserved_mb,
    CAST(SUM(ps.used_page_count) * 8.0 / 1024 AS DECIMAL(18,2)) AS used_mb,
    CAST(SUM(ps.reserved_page_count) * 8.0 / 1024 / 1024 AS DECIMAL(18,2)) AS reserved_gb
FROM sys.tables t
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
JOIN sys.dm_db_partition_stats ps
    ON t.object_id = ps.object_id
WHERE t.is_ms_shipped = 0
--   AND s.name = 'APPUSER' -- APPUSER만 조회시 
GROUP BY
    s.name,
    t.name
ORDER BY
    reserved_mb DESC;

결과는:

schema_name   table_name       row_count    reserved_mb   used_mb   unused_mb
------------  ---------------  -----------  ------------  --------  ----------
dbo           ORDER_HISTORY    125000000    18500.23      18120.10  380.13
dbo           ORDERS           35000000     6200.50       6100.25   100.25
hr            EMPLOYEE         2500000      850.75        820.12    30.63
sales         CUSTOMER         1200000      420.50        400.21    20.29

EXEC sp_spaceused 'Users';

• 인덱스 정보 조회

EXEC sp_helpindex Users;