Computer/MSSQL

테이블 용량 및 row 수 확인

알찬돌삐 2017. 10. 30. 14:10
1. 테이블별 사용 용량

SELECT CONVERT(VARCHAR(30), MIN(o.name)) AS t_name
, LTRIM(STR(SUM(reserved) * 8192.0 / 1024.0, 15, 0)) AS t_size
, 'KB' AS UNIT
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 t_name ASC


2. 용량별 소팅

SELECT table_name = convert(varchar(30), min(o.name)) 
, table_size = convert(int, ltrim(str(sum(reserved) * 8.192 / 1024., 15, 0)))
, 'MB' AS UNIT
FROMsysindexes 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

SELECT CONVERT(VARCHAR(30), MIN(o.name)) AS t_name
, LTRIM(STR(SUM(reserved) * 8192.0 / 1024.0, 15, 0) + ' KB') AS t_size
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 SUM(reserved) * 8192.0 / 1024.0 DESC


3. 테이블별 Row 수

SELECT o.name, i.rows
FROM sysindexes i INNER JOIN sysobjects o ON i.id = o.id
WHERE i.indid < 2 AND o.xtype = 'U'
ORDER BY i.rows DESC


'Computer > MSSQL' 카테고리의 다른 글

저장프로시저(SP)에서 RecordCount -1 반환  (0) 2017.10.31
DATETIME 변환  (0) 2017.10.31
MSSQL 스크립트로 테이블 열 설명 붙이기  (0) 2011.12.20
페이징 처리  (0) 2011.11.29