데이터베이스에 포함된 전체 테이블의 크기(size)를 정렬하여 확인하는 방법에 대해서 아래와 같이 정리하였습니다.
USE AdventureWorks
GO
-- name, rows, reserved, data, index_size, unused
CREATE TABLE ##TableSize
(
TS_name nvarchar(128),
TS_rows char(11),
TS_reserved varchar(18),
TS_data varchar(18),
TS_index_size varchar(18),
TS_unused varchar(18)
)
INSERT INTO ##TableSize EXEC('sp_msforeachtable ''sp_spaceused "?"''');
-- reserved 정렬
SELECT * FROM ##TableSize
ORDER BY CAST(LEFT(TS_reserved, CHARINDEX(' KB', TS_reserved)) as int) DESC
DROP TABLE ##TableSize
GO
-- name, rows, reserved, data, index_size, unused
CREATE TABLE ##TableSize
(
TS_name nvarchar(128),
TS_rows char(11),
TS_reserved varchar(18),
TS_data varchar(18),
TS_index_size varchar(18),
TS_unused varchar(18)
)
INSERT INTO ##TableSize EXEC('sp_msforeachtable ''sp_spaceused "?"''');
-- reserved 정렬
SELECT * FROM ##TableSize
ORDER BY CAST(LEFT(TS_reserved, CHARINDEX(' KB', TS_reserved)) as int) DESC
DROP TABLE ##TableSize
[참고자료]
sp_spaceused(Transact-SQL)
http://msdn.microsoft.com/ko-kr/library/ms188776.aspx
사용자 데이터베이스 전체 테이블과 rows 확인
http://laigo.kr/461
sp_MSforeachdb, sp_MSforeachtable 프로시저 활용
http://laigo.kr/307
작성자 : Lai Go / 작성일자 : 2010.02.04
