[DATABASE/SQLSERVER] 전체 인덱스 스크립트 조회하기

■ 전체 인덱스 스크립트를 조회하는 방법을 보여준다.

 

▶ 예제 코드 (SQL)

SELECT
    A.object_id              AS ObjectID
   ,OBJECT_NAME(A.object_id) AS TableName
   ,A.index_id               AS IndexID
   ,A.name                   As IndexName
   ,CAST
    (
        CASE
            WHEN A.[type] = 1 AND is_unique = 1 THEN 'CREATE UNIQUE CLUSTERED INDEX '
            WHEN A.[type] = 1 AND is_unique = 0 THEN 'CREATE CLUSTERED INDEX '
            WHEN A.[type] = 2 AND is_unique = 1 THEN 'CREATE UNIQUE NONCLUSTERED INDEX '
            WHEN A.[type] = 2 AND is_unique = 0 THEN 'CREATE NONCLUSTERED INDEX '
        END                                 +
        QUOTENAME(A.name)                   +
        ' ON '                              +
        QUOTENAME(S.name)                   +
        '.'                                 +
        QUOTENAME(OBJECT_NAME(A.object_id)) +
        ' ('                                +
        STUFF
        (
            (
                SELECT
                    ',[' + COL_NAME(A.object_id, C.column_id) + CASE WHEN C.is_descending_key = 1 THEN '] DESC' ELSE '] ASC' END
                FROM   sys.index_columns C WITH(NOLOCK)
                WHERE  A.object_id          = C.object_id
                AND    A.index_id           = C.index_id
                AND    C.is_included_column = 0
                ORDER BY C.key_Ordinal ASC
                FOR XML PATH('')
            ), 1, 1, ''
        )    +
        ') ' +
        CASE WHEN A.[type] = 1 THEN '' ELSE COALESCE
        (
            'INCLUDE (' +
            STUFF
            (
                (
                    SELECT ',' + QUOTENAME(COL_NAME(A.object_id, C.column_id))
                    FROM   sys.index_columns C WITH(NOLOCK)
                    WHERE  A.object_id          = C.object_id
                    AND    A.index_id           = C.index_id
                    AND    C.is_included_column = 1
                    ORDER BY C.index_column_id ASC
                    FOR XML PATH('')
                ), 1, 1, ''
            ) +
            ') ',
            ''
        ) END +
        CASE WHEN A.has_filter = 1 THEN 'WHERE ' + A.filter_definition ELSE '' END +
        ' WITH (DROP_EXISTING = OFF, SORT_IN_TEMPDB = ON' +
        ', FILLFACTOR = ' +
        CAST(CASE WHEN fill_factor = 0 THEN 100 ELSE fill_factor END AS VARCHAR(3)) +
        CASE WHEN A.is_padded          = 1 THEN ', PAD_INDEX = ON'               ELSE ', PAD_INDEX = OFF'               END +
        CASE WHEN D.no_recompute       = 1 THEN ', STATISTICS_NORECOMPUTE  = ON' ELSE ', STATISTICS_NORECOMPUTE  = OFF' END +
        CASE WHEN A.[ignore_dup_key]   = 1 THEN ', IGNORE_DUP_KEY = ON'          ELSE ', IGNORE_DUP_KEY = OFF'          END +
        CASE WHEN A.[allow_row_locks]  = 1 THEN ', ALLOW_ROW_LOCKS = ON'         ELSE ', ALLOW_ROW_LOCKS = OFF'         END +
        CASE WHEN A.[allow_page_locks] = 1 THEN ', ALLOW_PAGE_LOCKS = ON'        ELSE ', ALLOW_PAGE_LOCKS = OFF'        END +
        CASE
            WHEN P.data_compression   = 0 THEN ', DATA_COMPRESSION = NONE'
            WHEN P.[data_compression] = 1 THEN ', DATA_COMPRESSION = ROW'
            ELSE ', DATA_COMPRESSION = PAGE'
        END +
        ') ON ' +
        CASE WHEN C.[type] = 'FG' THEN QUOTENAME(C.name) ELSE QUOTENAME(C.name) + '(' + F.Partition_Column + ')' END + ';'
        AS NVARCHAR(MAX)
    ) As IndexCreateStatement
   ,C.name                                                                                                           AS FileGroupName
   ,'DROP INDEX ' + QUOTENAME(A.Name) + ' ON ' + QUOTENAME(S.name) + '.' + QUOTENAME(OBJECT_NAME(A.object_id)) + ';' AS IndexDropStatement
From       sys.indexes     A WITH(NOLOCK)
INNER JOIN sys.objects     B WITH(NOLOCK) ON  A.object_id     = B.object_id
INNER JOIN sys.schemas     S              ON  B.schema_id     = S.schema_id
INNER JOIN sys.data_spaces C WITH(NOLOCK) ON  A.data_space_id = C.data_space_id
INNER JOIN sys.stats       D WITH(NOLOCK) ON  A.object_id     = D.object_id
                                          AND A.index_id      = D.stats_id
INNER JOIN
(
    SELECT
        object_id
       ,index_id
       ,data_compression
       ,ROW_NUMBER() OVER(PARTITION BY object_id
       ,index_id ORDER BY COUNT(*) DESC) AS Main_Compression
    FROM sys.partitions WITH(NOLOCK)
    GROUP BY
        object_id
       ,index_id
       ,Data_Compression
) P ON  A.object_id        = P.object_id
    AND A.index_id         = P.index_id
    AND P.Main_Compression = 1
    OUTER APPLY
    (
        SELECT COL_NAME(A.object_id, E.column_id) AS Partition_Column
        FROM   sys.index_columns E WITH(NOLOCK)
        WHERE  E.object_id         = A.object_id
        AND    E.index_id          = A.index_id
        AND    E.partition_ordinal = 1
    ) F
WHERE  A.[type]       IN (1, 2)
AND    B.[type]       != 'S'
AND    is_primary_key =  0
AND    OBJECT_NAME(A.object_id) NOT LIKE 'queue_messages_%'
AND    OBJECT_NAME(A.object_id) NOT LIKE 'filestream_tombstone_%'
AND    OBJECT_NAME(A.object_id) NOT LIKE 'sys%'
OPTION(Recompile);