■ 전체 인덱스 스크립트 조회하기

----------------------------------------------------------------------------------------------------

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         =THEN ', PAD_INDEX = ON'               ELSE ', PAD_INDEX = OFF'               END +

        CASE WHEN D.no_recompute      =THEN ', STATISTICS_NORECOMPUTE  = ON' ELSE ', STATISTICS_NORECOMPUTE  = OFF' END +

        CASE WHEN A.[ignore_dup_key]  =THEN ', IGNORE_DUP_KEY = ON'          ELSE ', IGNORE_DUP_KEY = OFF'          END +

        CASE WHEN A.[allow_row_locks] = 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);

----------------------------------------------------------------------------------------------------

Posted by 사용자 icodebroker
TAG