[MSSQL] DB내의 모든 테이블 및 인덱스 현황

 

근 5년만에 다시 잡은 MSSQL..

기존 현황 조회에 사용하던건 한방이 아니기도 하고 예전 메타뷰들을 많이 사용해서

새롭게 한방 쿼리로 작성해보았다.

 

db_name부터 update_ymd까지가 테이블에 대한 정보이며
idx_type부터 idx_col_name까지가 해당 테이블의 개별 인덱스들에 대한 정보
테이블 정보만 조회하고자 할 경우 4번째 줄부터 시작하는 서브쿼리만 실행.

total_size_mb : 데이터 및 인덱스 포함 모든 크기
2nd_idx_cnt : 테이블데이터가 포함되어 있는 clustered index를 제외한 2차 인덱스들의 개수
2nd_idx_cnt : 테이블데이터가 포함되어 있는 clustered index를 제외한 2차 인덱스들의 크기
update_ymd : 테이블 데이터 마지막 변경일(insert,update,delete)
idx_type : 1(clustered), 2(non clustered)
idx_col_name : 복합 인덱스의 경우 컬럼 순서대로 콤마로 구분해서 대상 컬럼명 표시
last_user_use : insert나 update, delete가 아닌 조회에(scan, seek, lookup)에 사용된 마지막 일자

 

select a.db_name, a.schema_name, a.tbl_name, a.tbl_type, a.row_cnt, a.total_size_mb, a.[2nd_idx_cnt], a.[2nd_idx_size_mb], a.create_ymd, a.update_ymd
     , b.idx_type, b.is_primary_key, b.is_unique, b.idx_size_mb, b.idx_name, b.last_user_use_ymd, b.idx_col_cnt, b.idx_col_name
  from (
        select db_name(db_id()) as db_name
             , (select name from sys.schemas with(nolock) where schema_id = a.schema_id) as schema_name
             , a.name as tbl_name
             , case when min(b.index_id) = 0 then 'heap' else 'clustered' end as tbl_type
             , sum(case when (b.index_id < 2) then b.row_count else 0 end) as row_cnt
             , cast(sum(b.used_page_count) * 8.0 / 1024.0 as numeric(36, 2)) as total_size_mb
             , sum(case when b.index_id > 1 then 1 else 0 end) as [2nd_idx_cnt]
             , cast(sum(case when b.index_id < 2 then 0 else b.used_page_count end) * 8.0 / 1024.0 as numeric(36, 2)) as [2nd_idx_size_mb]
             , convert(varchar(10), a.create_date, 120) as create_ymd
             , convert(varchar(10), (select max(last_user_update) from sys.dm_db_index_usage_stats with(nolock) where object_id = a.object_id and database_id = db_id()), 120) as update_ymd
             , db_id() as db_id
             , a.schema_id
             , a.object_id
          from sys.tables a with(nolock)
          join sys.dm_db_partition_stats b with(nolock) on a.object_id = b.object_id
         group by a.schema_id, a.object_id, a.name, a.create_date
       ) a
  left join (
             select a.name as tbl_name
                  , b.type as idx_type
                  , b.is_primary_key
                  , b.is_unique
                  , b.index_id
                  , (
                     select sum(used_page_count)
                       from sys.dm_db_partition_stats with(nolock)
                      where object_id = b.object_id
                        and index_id = b.index_id
                    ) idx_size_mb
                  , b.name as idx_name
                  , convert(varchar(10), case when isnull(c.last_user_seek, '1900-01-01') > isnull(c.last_user_scan, '1900-01-01')
                                                   and isnull(c.last_user_seek, '1900-01-01') > isnull(c.last_user_lookup, '1900-01-01') then isnull(c.last_user_seek, '1900-01-01')
                                              when isnull(c.last_user_scan, '1900-01-01') > isnull(c.last_user_lookup, '1900-01-01') then isnull(c.last_user_scan, '1900-01-01')
                                              else isnull(c.last_user_scan, '1900-01-01')
                                          end
                                       , 120
                           ) as last_user_use_ymd
                  , (select count(*) from sys.index_columns with(nolock) where object_id = b.object_id and index_id = b.index_id) as idx_col_cnt
                  , stuff(( 
                           select ',' + bb.name
                             from sys.index_columns aa with(nolock)
                             join sys.columns bb with(nolock) on aa.object_id = bb.object_id
                                                             and aa.column_id = bb.column_id
                            where aa.object_id = b.object_id
                              and aa.index_id = b.index_id
                            order by aa.key_ordinal
                              for xml path('')
                          ), 1, 1, ''
                         ) as idx_col_name
                  , a.object_id
               from sys.tables a with(nolock)
               join sys.indexes b with(nolock) on a.object_id = b.object_id
                                              and b.type in ('1', '2')
               left join sys.dm_db_index_usage_stats c with(nolock) on b.object_id = c.object_id
                                                                   and b.index_id = c.index_id
                                                                   and c.database_id = db_id()
            ) b on a.object_id = b.object_id
 order by a.total_size_mb desc, b.idx_col_name;