sql-scripts/OCTPDBA-414 - proc to align indexes/generate inserts.sql

USE Arizona;
GO

DELETE FROM upd.IA_IndexAlignementActions
WHERE IA_schema_name = 'dbo.Criteria';

BEGIN TRANSACTION;
WITH ColInfo
AS (SELECT TblName = [o].[name],
           SchemaTbl = s.name + '.' + [o].[name],
           IndexName = [i].[name],
           s.name AS schemaName,
           IsPrimaryKey = i.is_primary_key,
           IsUniqueConstraint = i.is_unique_constraint,
           IsUnique = i.is_unique,
           CASE
               WHEN i.type = 1 THEN
                   1
               ELSE
                   0
           END AS is_clustered,
           ColName = c.name,
           IsComputedCol = c.is_computed,
           IsIncludedCol = [ic].is_included_column,
           [ic].key_ordinal,
           FilterDefinition = i.filter_definition,
           i.object_id,
           i.ignore_dup_key,
           i.fill_factor,
           i.is_padded
    FROM [sys].objects o
        JOIN [sys].schemas s
            ON o.schema_id = s.schema_id
        JOIN [sys].columns c
            ON o.object_id = c.object_id
        JOIN [sys].indexes i
            ON c.object_id = i.object_id
        JOIN [sys].index_columns ic
            ON i.index_id = [ic].index_id
               AND o.object_id = [ic].object_id
               AND c.column_id = [ic].column_id
    WHERE [o].[name] = 'criteria'
          AND s.name = 'dbo')
SELECT DISTINCT
       '
INSERT INTO arizona.upd.IA_IndexAlignementActions
(
    IA_executionDate,
    IA_schema_name,
    IA_table_name,
    IA_columns_name,
    IA_is_clustered,
    IA_is_unique,
    IA_included_columns,
    IA_is_to_be_deleted,
    IA_is_to_be_recreated,
    IA_expected_index_name,
    IA_foundIndexName,
    IA_wasIndexFound,
    IA_wasCreated,
    IA_wasRenamed,
    IA_wasDeleted,
    IA_wasRecreated,
    IA_includedColumnsMatch,
    IA_actualColumns,
    IA_actualIncludedColumns,
    IA_statusMsg,
    IA_index_options,
    IA_reference
)
SELECT
    NULL as IA_executionDate,
    ''' + [x].schemaName + ''' as IA_schema_name,
    ''' + [x].TblName + ''' as IA_table_name,
    ''' + c.IndexColumns + ''' as IA_columns_name,
    0 as IA_is_clustered,
    ' + CASE
            WHEN [x].IsUnique = 0 THEN
                '0'
            ELSE
                '1'
        END + ' as IA_is_unique,
    '  + CASE
             WHEN ci.IncludedColumns IS NOT NULL THEN
                 '''' + ci.IncludedColumns + ''''
             ELSE
                 'NULL'
         END + ' as IA_included_columns,
    0 as  IA_is_to_be_deleted,
    0 as IA_is_to_be_recreated,
    ''' + [x].IndexName
       + ''' as IA_expected_index_name,
    NULL as IA_foundIndexName,
    0 as IA_wasIndexFound,
    0 as IA_wasCreated,
    0 as IA_wasRenamed,
    0 as IA_wasDeleted,
    0 as IA_wasRecreated,
    NULL as IA_includedColumnsMatch,
    NULL as IA_actualColumns,
    NULL as IA_actualIncludedColumns,
    NULL as IA_statusMsg,
    ''' + CASE
              WHEN INDEXPROPERTY([x].object_id, [x].IndexName, 'IsStatistics') = 1 THEN
                  'STATISTICS_NORECOMPUTE = ON, '
              ELSE
                  'STATISTICS_NORECOMPUTE = OFF, '
          END + CASE
                    WHEN [x].ignore_dup_key = 1 THEN
                        'IGNORE_DUP_KEY = ON, '
                    ELSE
                        'IGNORE_DUP_KEY = OFF, '
                END + CASE
                          WHEN [x].is_padded = 1 THEN
                              'PAD_INDEX = ON,'
                          ELSE
                              ''
                      END + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST([x].fill_factor AS VARCHAR(3))
       + ''' as IA_index_options,
       NULL as IA_reference
WHERE NOT EXISTS(
    SELECT 1
    FROM arizona.upd.IA_IndexAlignementActions s
    WHERE s.IA_executionDate IS NULL
    AND s.IA_schema_name = ''' + [x].schemaName + '''
    AND s.IA_table_name = ''' + [x].TblName + '''
    AND s.IA_columns_name = ''' + c.IndexColumns + '''
    '  + CASE
             WHEN ci.IncludedColumns IS NOT NULL THEN
                 'AND s.IA_included_columns = ''' + ci.IncludedColumns + '''   '
             ELSE
                 ''
         END + '
)
'   AS cmd,
       [x].IndexName,
       c.IndexColumns,
       ci.IncludedColumns
FROM ColInfo x
    CROSS APPLY
(
    SELECT IndexColumns = STUFF([sq].strXML, 1, 2, '')
    FROM
    (
        SELECT ', ' + ISNULL([x2].ColName, '')
        FROM ColInfo x2
        WHERE [x].TblName = [x2].TblName
              AND [x].schemaName = [x2].schemaName
              AND [x].IndexName = [x2].IndexName
              AND [x2].IsIncludedCol = 0
        ORDER BY [x2].key_ordinal
        FOR XML PATH('')
    ) sq(strXML)
) c
    OUTER APPLY
(
    SELECT IncludedColumns = STUFF([sq].strXML, 1, 2, '')
    FROM
    (
        SELECT ', ' + ISNULL([x2].ColName, '')
        FROM ColInfo x2
        WHERE [x].TblName = [x2].TblName
              AND [x].schemaName = [x2].schemaName
              AND [x].IndexName = [x2].IndexName
              AND [x2].IsIncludedCol = 1
        ORDER BY [x2].key_ordinal
        FOR XML PATH('')
    ) sq(strXML)
) ci;


ROLLBACK TRANSACTION;