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

• 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,
    SUM(ps.reserved_page_count) * 8.0 / 1024 AS reserved_mb,
    SUM(ps.used_page_count) * 8.0 / 1024 AS used_mb,
    (SUM(ps.reserved_page_count) - SUM(ps.used_page_count)) * 8.0 / 1024 AS unused_mb
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
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

</source>

EXEC sp_spaceused 'Users';

• 인덱스 정보 조회

EXEC sp_helpindex Users;