[mssql] 테이블 별 데이터 건 수 및 사이즈 확인

 

 

출처 : http://www.sqler.com/266501

 

사이즈 모니터링 관련 글들이 대세(?) 인듯 하여 관련 내용으로 이어서 써봅니다.

아래 쿼리는 2005 이상에서 각 테이블별 데이터 건수를 조회할 때 유용 합니다.

(성능 상 큰 부담을 주지 않고도 테이블의 데이터 건수 및 테이블 크기의 조회가 가능 합니다.)

원본 출처는 여기 입니다

-- SQL 2005
SELECT Schema_name(tbl.schema_id)                                 AS [Schema],
       tbl.name,
       Coalesce((SELECT pr.name
                 FROM   sys.database_principals pr WITH (nolock)
                 WHERE  pr.principal_id = tbl.principal_id),
       Schema_name(tbl.schema_id))                                AS [Owner],
       tbl.max_column_id_used                                     AS [Columns],
       CAST(CASE idx.index_id
              WHEN 1 THEN 1
              ELSE 0
            END AS BIT)                                           AS
       [HasClusIdx],
       Coalesce((SELECT SUM (spart.ROWS)
                 FROM   sys.partitions spart WITH (nolock)
                 WHERE  spart.object_id = tbl.object_id
                        AND spart.index_id < 2), 0)               AS [RowCount],
       Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE
                                         WHEN a.TYPE <> 1 THEN a.used_pages
                                         WHEN p.index_id < 2 THEN a.data_pages
                                         ELSE 0
                                                                           END)
                 FROM   sys.indexes AS i WITH (nolock)
                        JOIN sys.partitions AS p WITH (nolock)
                          ON p.object_id = i.object_id
                             AND p.index_id = i.index_id
                        JOIN sys.allocation_units AS a WITH (nolock)
                          ON a.container_id = p.partition_id
                 WHERE  i.object_id = tbl.object_id), 0.0) / 1024 AS [IndexMB],
       Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE
                        WHEN a.TYPE <> 1 THEN a.used_pages
                                 WHEN p.index_id < 2 THEN a.data_pages
                                 ELSE 0
                                                            END)
                 FROM   sys.indexes AS i WITH (nolock)
                        JOIN sys.partitions AS p WITH (nolock)
                          ON p.object_id = i.object_id
                             AND p.index_id = i.index_id
                        JOIN sys.allocation_units AS a WITH (nolock)
                          ON a.container_id = p.partition_id
                 WHERE  i.object_id = tbl.object_id), 0.0) / 1024 AS [DataMB],
       tbl.create_date,
       tbl.modify_date
FROM   sys.tables AS tbl WITH (nolock)
       INNER JOIN sys.indexes AS idx WITH (nolock)
         ON ( idx.object_id = tbl.object_id
              AND idx.index_id < 2 )
       INNER JOIN MASTER.dbo.spt_values v WITH (nolock)
         ON ( v.NUMBER = 1
              AND v.TYPE = 'E' )
--WHERE tbl.Name like '%tablename%' 
ORDER  BY 8 DESC 

SQL 2000이라면 아래 쿼리 형태로도 가능 합니다. 

SELECT *

FROM   (SELECT table_catalog = Db_name(),
               table_schema = User_name(o.uid),
               table_name = o.name,
               table_type = CONVERT(NVARCHAR(30), CASE o.TYPE WHEN 'U' THEN CASE
                            WHEN
                            Objectproperty(o.id,
                            'IsMSShipped') = 0 THEN N'TABLE' ELSE
                            N'SYSTEM TABLE' END
                            WHEN 'S'
                            THEN
                            N'SYSTEM TABLE' WHEN 'V' THEN CASE WHEN
                            Objectproperty(o.id,
                            'IsMSShipped') = 0
                            THEN N'VIEW' ELSE N'SYSTEM VIEW' END END),
               table_guid = CONVERT(UNIQUEIDENTIFIER, NULL),
               bookmarks = CONVERT(BIT, 1),
               bookmark_type = CONVERT(INT, 1 /*DBPROPVAL_BMK_NUMERIC*/),
               bookmark_datatype = CONVERT(SMALLINT, 21 /*DBTYPE_UI8 */),
               bookmark_maximum_length = CONVERT(INT, 8),
               bookmark_information = CONVERT(INT, 0),
               table_version = CONVERT(BIGINT, Objectpropertyex(o.id,
                               'objectversion'))
                      ,
               cardinality = x.ROWS,
               DESCRIPTION = CONVERT(NVARCHAR(1), NULL),
               table_propid = CONVERT(INT, NULL)
        FROM   sysobjects o
               LEFT JOIN sysindexes x
                 ON o.id = x.id
                    AND x.indid IN ( 0, 1 )
        WHERE  o.TYPE IN ( 'U', 'V', 'S' )
               AND Permissions(o.id) <> 0) AS t
--WHERE  ( @table_schema IS NULL 
--          OR @table_schema = table_schema ) 
--       AND ( @table_type IS NULL 
--              OR @table_type = table_type ) 
ORDER  BY 4,
          2,
          12 DESC