인덱스 정보 조회

/*

MSSQL 공통버전

*/

-- 인덱스 정보 조회

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, 10) 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, 11) 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, 12) 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, 13) 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, 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

--전체 인덱스 정보 조회

SELECT * FROM dbo.idxInfo

GO

--Heap 구조의(Clustered Index가 없는) 테이블 조회

SELECT * FROM dbo.idxInfo

WHERE ClusterType = 'HEAP'

GO

--AdventureWorks 의 dbo.Employee 테이블 인덱스 조회

SELECT * FROM dbo.idxInfo

WHERE Owner = 'head1ton'

    AND TableName = 'Employee'

GO

/*

MSSQL 2005 이상 버전

*/

--인덱스 정보 조회

IF EXISTS (SELECT [name]

             FROM sys.sysobjects

             WHERE [type] = 'V'

                AND [name] = 'idxInfo')

BEGIN

    DROP VIEW [dbo].[idxInfo]

END

GO

ALTER 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 [fillfactor]

    , 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

-- 전체 인덱스 정보 조회

SELECT * FROM dbo.idxInfo;

GO

-- Heap구조의 (CLUSTERED INDEX가 없는) 테이블 조회

SELECT *

FROM dbo.idxInfo

WHERE index_type = 'HEAP'

GO

-- 특정 테이블의 인덱스 조회

SELECT *

FROM dbo.idxInfo

WHERE [OBJECT_ID] = OBJECT_ID('schema_name.table_name')

GO

--or

SELECT *

FROM dbo.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 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 [fillfactor]

    , 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

-- 전체 인덱스 정보 조회

SELECT * FROM dbo.idxInfo2;

GO

-- Heap구조의 (CLUSTERED INDEX가 없는) 테이블 조회

SELECT *

FROM dbo.idxInfo2

WHERE index_type = 'HEAP'

GO

-- 특정 테이블의 인덱스 조회

SELECT *

FROM dbo.idxInfo2

WHERE [OBJECT_ID] = OBJECT_ID('schema_name.table_name')

GO

--or

SELECT *

FROM dbo.idxInfo

WHERE [SCHEMA_NAME] = 'schema_name'

    AND [TABLE_NAME] = 'table_name'

GO