MS SQL Server 테이블 건수 용량 확인 쿼리

---------------------------------------------------------------------------------

-- table 건수

select  a.name

       ,a.id

       ,b.rows

from    sysobjects   a

        left outer join sysindexes b on b.id = a.id

where   a.xtype = 'u'

and     b.indid < 2

order by b.rows desc

 

 

--------------------------------------------------------------------------------

-- table 용량

select  a.id, a.name

       ,str(sum(reserved) * 8192.0 / 1024.0, 15, 0) + ' kb'     as t_size

from    sysobjects   a

        inner join sysindexes b on b.id = a.id

where   a.xtype = 'u'

and     b.indid in (0, 1, 255)

group by a.id, a.name

order by sum(reserved) desc

 

 

--------------------------------------------------------------------------------

-- 테이블 건수 + 용량

select  a.schemaName

       ,a.tableName

       ,a.rowCnt

       ,str(a.reservedPage * 8, 15, 0) + ' kb'  as reserved

       ,str(a.dataPage * 8, 15, 0) + ' kb'          as data

       ,str((case when a.usedPage     > a.dataPage then (a.usedPage     - a.dataPage) else 0 end) * 8, 15, 0) + ' kb'   as indexSize

       ,str((case when a.reservedPage > a.usedpage then (a.reservedPage - a.usedPage) else 0 end) * 8, 15, 0) + ' kb'   as unused

from   (

        select  a.schema_id

               ,schema_name(a.schema_id)        as schemaName

               ,object_name(a.object_id)        as tableName

               ,sum (b.reserved_page_count)     as reservedPage

               ,sum (b.used_page_count)         as usedPage

               ,sum (case when (b.index_id < 2) then (b.in_row_data_page_count + b.lob_used_page_count + b.row_overflow_used_page_count)

                          else 0 

                     end )                      as dataPage

               ,sum (case when (b.index_id < 2) then b.row_count else 0 end ) as rowCnt

        from    sys.objects a

                inner join  sys.dm_db_partition_stats   b on (b.object_id = a.object_id)

        --where   schema_name(a.schema_id) = 'dbo'

        group by a.schema_id, a.object_id

       ) a

where   a.schemaName = 'dbo'

order by rowCnt desc

 

 

 

출처 : https://m.blog.naver.com/supercrat/221645769431