인덱스 정보를 조회하는 뷰

MS SQL Server에서 인덱스 정보를 조회하기 위해서는 Enterprise Manager(2000 버전 이전), SQL Server Managment Studio(2005 버전 이후)와 같은 GUI 관리 프로그램이나 sp_help 혹은 sp_helpindex와 같은 시스템 저장 프로시저를 사용해서 조회해야 한다. 하지만 이러한 툴들은 출력 형식이 정해져 있고 테이블 단위로 정보를 보여주기 때문에 보고서 용도로 사용하기에는 다소 불편함을 느낄수 있을 것이다. 내가 원하는 형식으로 인덱스 정보를 조회할 수 있는 방법이 없을까 고민하던 중 MS SQL Server에서 인덱스 정보를 조회하는 뷰를 작성하는 스크립트를 발견하였다. 블로그 주인장은 아래와 같이 원래의 스크립트를 수정하여 유용하게 사용하고 있다.  관심이 있는 분들은 각자의 구미에 맞게 스크립트를 수정하여 사용해 보길 바란다.

 

본 스크립트는 SQLLeader.com의 스크립트 자료실에서 얻은 스크립트를 수정한 것으로 작성자는 avigneau이다. 스크립트 테스트는 SQL Server 6.X, 7.0, 2000버전까지 이루어진 것  같다(참고로 주인장은 2005버전에 적용해 아무 이상 없이 사용하고 있다).

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.id AS [TableId]

     , u.name AS [Owner]

     , o.name AS [TableName]

     , i.indid AS [IndexID]

     , CASE i.name WHEN o.name THEN '** NONE **'

                   ELSE i.name END AS [IndexName]

     , CASE i.indid WHEN 1 THEN 'CLUSTERED'

                    WHEN 0 THEN 'HEAP'

                    ELSE 'NONCLUSTERED' END AS [ClusterType]

     , CASE WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'

            WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'

            ELSE '' END AS [UniqueType]

     , CASE WHEN (i.status & (2048)) > 0 OR ((i.status & (4096)) > 0 ) THEN 'CONSTRAINT'

            WHEN i.indid = 0 THEN ' '

            ELSE 'INDEX' END AS [IndexType]

     , CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1) IS NULL THEN ''

            ELSE INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 1) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,2) END +

       CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 3) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,3) END +

       CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 4) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,4) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,5) END +

       CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 6) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,6) END +

       CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 7) END +

       CASE WHEN INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid, 8) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,8) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,9) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,10) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,11) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,12) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,13) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,14) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,15) END +

       CASE WHEN INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16) IS NULL THEN ''

            ELSE ', '+INDEX_COL(u.name+'.'+QUOTENAME(o.name), i.indid,16) END AS [AllColName]

     , OrigFillFactor AS [FillFactor]

FROM sysobjects o (NOLOCK)

    LEFT OUTER JOIN sysindexes i (NOLOCK)

        ON o.id = i.id

   JOIN sysusers u (NOLOCK)

        ON o.uid = u.uid

WHERE o.type = 'U' AND i.indid < 255

AND o.name NOT IN ('dtproperties')

AND i.name NOT LIKE '_WA_Sys_%'  -- because of SQL Server 7.0

GO

 

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

SELECT *

  FROM dbo.idxinfo

GO

 

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

SELECT *

  FROM dbo.idxinfo

 WHERE ClusterType = 'HEAP'

GO

 

실행 예3  dbo.Employee 테이블의 인덱스 조회

SELECT *

  FROM dbo.idxinfo

 WHERE Owner = 'dbo'

   AND TableName = 'Employee'

GO