DB 전체/테이블 별 용량 조회

DB 테이블별 용량 조회

방법1> 전체 용량

SELECT b.groupname AS [File Group],

    Name,

    Filename,

    CONVERT (Decimal(15,2), ROUND(a.Size/128.0, 2)) [할당된 용량 (MB)],

    CONVERT (Decimal(15,2) , ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.0, 2)) AS [사용중인 용량 (MB)],

    CONVERT (Decimal(15,2) , ROUND((a.Size - FILEPROPERTY(a.Name,'SpaceUsed'))/128.0, 2)) AS [사용가능한 용량 (MB)]

FROM dbo.sysfiles a

JOIN sysfilegroups b

ON a.groupid = b.groupid

ORDER BY b.groupname

 

방법2> 테이블별 용량

SELECT table_name = convert(varchar(30), min(o.name)),
       table_size = convert(int, ltrim(str(sum(reserved) * 8.192 / 1024., 15, 0))),
       UNIT = 'MB'
FROM sysindexes i 
INNER JOIN sysobjects o ON (o.id = i.id) 
WHERE i.indid in (0, 1, 255) 
AND o.xtype = 'U' 
GROUP BY i.id 
ORDER BY 2 desc