[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);

100

101

102

103

104

105

106

107

108

109

110

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);