본문 바로가기
㏈ª By β┖υΕJini/MS-SQL

MS-SQL 테이블별 용량 보기

by ㏈ª ☞ β┖υΕJini.κR 2006. 10. 17.

use pubs
go

select table_name = convert(varchar(30), min(o.name)), table_size = ltrim(str(sum(reserved) * 8192 / 1024.,15,0)
+ '' KB'')
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
go

이러한 방법으로 쿼리하시면 됩니다.

가끔씩 사용되는 루틴이라면 아래와 같이 master db에 프로시져로 만들어 등록시킨후

--drop proc sp_sqler_getTableSize
create proc sp_sqler_getTableSize @dbname sysname
as
   declare @stmt nvarchar(4000)
   set @stmt = 'USE ' + @dbname + ';
                select table_name = convert(varchar(30), min(o.name)), table_size = ltrim(str(sum(reserved) *
8192 / 1024.,15,0) + '' KB'')
                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) desc'

   exec sp_executesql @stmt
go


exec sp_sqler_getTableSize 'pubs'