테이블별 데이터 개수, 인덱스 사이즈, 생성일, 마지막 수정일을 알고 싶을때...

SQL 작업을 하다보면 테이블이 언제 생성 되었으며, 마지막으로 수정된 날짜 및 인덱스 사이즈을 알고 싶을 때가 있다.

적용 버전은 MSSQL2005, MSSQL2008 버전임.
2000 버전은 프로파일러를 돌려서 방법 외에는 없는 듯함.

--1. 특정 테이블의 정보를 간단하게 정보 DISPLAY
SELECT * FROM sys.dm_db_index_usage_stats WHERE object_id = OBJECT_ID('dbo.TN_KOREAN_WORD');

출처 : http://blog.naver.com/PostView.nhn?blogId=nebadak&logNo=90085971368&redirect=Dlog&widgetTypeCall=true

-- 2. 테이블별 데이터 개수, 인덱스 사이즈, 생성일, 마지막 수정일 DISPLAY
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 

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