연구개발/SQL2005

인덱스 정보를 조회하는 뷰 New

HEAD1TON 2011. 7. 18. 00:54

이전 포스트에서 idxinfo라 명명한 인덱스 정보를 조회하는 뷰는 소개한 바 있다.

 

이번 포스트에서는 SQL Server 2005 이상에서 sys.objects 및 sys.indexes Object Catalog View를 이용하여 뷰를 재구성해 보았다.

IF EXISTS (SELECT [name]
             FROM sys.sysobjects
            WHERE [type] = 'V'
              AND [name] = 'idxinfo')
BEGIN
    DROP VIEW [dbo].[idxinfo]
END
GO
 
CREATE VIEW [dbo].[idxinfo]
AS
SELECT o.[schema_id]              AS [schema_id]
     , schema_name(o.[schema_id]) AS [schema_name]
     , o.[object_id]              AS [object_id]
     , o.[name]                   AS [table_name]
     , i.[name]                   AS [index_name]
     , i.[type_desc]              AS [index_type]
     , CASE i.[is_unique] WHEN 1 THEN 'Unique'
                          WHEN 0 THEN 'Non-unique'
                          ELSE '' END AS [uniqueness]
     , CASE WHEN i.[is_primary_key] = 1 THEN 'PK'
            WHEN i.[is_unique_constraint] = 1 THEN 'UK'
            ELSE '' END AS [constraint]
     , CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) IS NULL
            THEN ''
            ELSE index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16)
       END AS [index_columns]
     , i.[fill_factor] AS [fillfator]
     , CASE i.[is_disabled] WHEN 0 THEN 'Active' 
                            WHEN 1 THEN 'Inactive' END AS [is_disabled]
  FROM sys.objects o WITH(NOLOCK)
  LEFT OUTER JOIN
       sys.indexes i WITH(NOLOCK)
    ON o.[object_id] = i.[object_id]
 WHERE o.[type] = 'U'
   AND i.[index_id] < 255
GO

뷰의 조회방법은 동일하다.

 

실행 예1 전체 인덱스 정보 조회 

SELECT *
  FROM dbo.idxinfo
GO

 

실행 예2 Heap 구조의(Clustered Index가 없는) 테이블 조회 

SELECT *
  FROM dbo.idxinfo
 WHERE ClusterType = 'HEAP'
GO

 

실행 예3  특정 테이블의 인덱스 조회

SELECT * 
  FROM idxinfo
 WHERE [object_id] = object_id('schema_name.table_name')
GO
 
--or
SELECT *
  FROM idxinfo
 WHERE [schema_name] = 'schema_name' 
   AND [table_name] = 'table_name'
GO

 

아래의 뷰 는 sys.dm_db_index_physical_stats Dynamic Management Function을 사용하여 인덱스의 물리적 통계정보를 함께 조회한다.

IF EXISTS (SELECT [name]
             FROM sys.sysobjects
            WHERE [type] = 'V'
              AND [name] = 'idxinfo2')
BEGIN
    DROP VIEW [dbo].[idxinfo2]
END
GO
 
CREATE VIEW [dbo].[idxinfo2]
AS
SELECT o.[schema_id]              AS [schema_id]
     , schema_name(o.[schema_id]) AS [schema_name]
     , o.[object_id]              AS [object_id]
     , o.[name]                   AS [table_name]
     , i.[name]                   AS [index_name]
     , i.[type_desc]              AS [index_type]
     , CASE i.[is_unique] WHEN 1 THEN 'Unique'
                          WHEN 0 THEN 'Non-unique'
                          ELSE '' END AS [uniqueness]
     , CASE WHEN i.[is_primary_key] = 1 THEN 'PK'
            WHEN i.[is_unique_constraint] = 1 THEN 'UK'
            ELSE '' END AS [constraint]
     , CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) IS NULL
            THEN ''
            ELSE index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 1) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 2) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 3) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 4) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 5) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 6) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 7) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 8) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 9) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 10) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 11) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 12) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 13) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 14) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 15) END
     + CASE WHEN index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16) IS NULL
            THEN ''
            ELSE ', ' + index_col(schema_name(o.[schema_id]) + '.' + o.[name], i.index_id, 16)
       END AS [index_columns]
     , i.[fill_factor] AS [fillfator]
     , CASE i.[is_disabled] WHEN 0 THEN 'Active'
                            WHEN 1 THEN 'Inactive' END AS [is_disabled]
     , [st].[index_depth]  AS [index_depth]
     , [st].[index_level]  AS [index_level]
     , [st].[page_count]   AS [page_count]
     , ROUND([st].[avg_fragmentation_in_percent], 2) AS [avg_fragmentation_in_percent]
  FROM sys.objects o WITH(NOLOCK)
  LEFT OUTER JOIN
       sys.indexes i WITH(NOLOCK)
    ON o.[object_id] = i.[object_id]
  LEFT JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , 'SAMPLED') st
    ON [i].[object_id] = [st].[object_id]
   AND [i].[index_id] = [st].[index_id]
 WHERE o.[type] = 'U'
   AND i.[index_id] < 255
GO

저작자표시 (새창열림)