데이터베이스별 용량 쿼리문

SQL 2007/10/16 15:02
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'

  exec sp_executesql @stmt
go


exec sp_sqler_getTableSize 'pubs'


참조 : sqler.pe.kr
Trackback 0 : Comment 0

Trackback Address :: http://reboot.co.kr/trackback/247

Write a comment