SQL Server: batch migrate sync table

-- USE DB_DEV;
-- USE DB_QA;
-- USE DB_PROD;
 
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

    -- =============================================
    -- 1. CONFIGURATION
    -- =============================================
    DECLARE @SchemaName NVARCHAR(128) = ''
    DECLARE @TableName NVARCHAR(128) = 'wp_posts'

    -- Table Description
    DECLARE @TableDesc NVARCHAR(4000) = N'สำหรับเก็บ post';

    -- Check for NULL or Empty Schema -> Default to 'dbo'
    IF @SchemaName IS NULL OR LTRIM(RTRIM(@SchemaName)) = ''
        SET @SchemaName = 'dbo';

    -- Standard Variables
    DECLARE @TempTableName NVARCHAR(128) = @TableName + '_dup'
    DECLARE @OldTableName NVARCHAR(128) = @TableName + '_old'
    DECLARE @TargetObjID INT = OBJECT_ID(QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName))

    -- Dynamic SQL Variables
    DECLARE @SqlDropFK NVARCHAR(MAX) = '', @SqlCreateFK NVARCHAR(MAX) = '', @SqlCreatePK_Indexes NVARCHAR(MAX) = ''
    DECLARE @CreateTableSQL NVARCHAR(MAX), @CopySQL NVARCHAR(MAX), @DropOldSQL NVARCHAR(MAX)
    DECLARE @ColList NVARCHAR(MAX) = '', @SharedColList NVARCHAR(MAX) = '', @Msg NVARCHAR(MAX)
    DECLARE @IdentityColName NVARCHAR(128) = NULL
    DECLARE @PreserveIdentity BIT = 0
    DECLARE @CurrentFullName NVARCHAR(256), @TempFullName NVARCHAR(256)

    -- Cursor Helpers
    DECLARE @Cur_ColName NVARCHAR(128), @Cur_DataType NVARCHAR(100), @Cur_IsNullable CHAR(1), @Cur_Desc NVARCHAR(4000)
    DECLARE @Cur_FKID INT, @Cur_FKName NVARCHAR(128), @Cur_ParentSchema NVARCHAR(128), @Cur_ParentTable NVARCHAR(128)
    DECLARE @Helper_FKCols NVARCHAR(MAX), @Helper_RefCols NVARCHAR(MAX)
    DECLARE @Cur_IdxID INT, @Cur_IdxName NVARCHAR(128), @Cur_IsPK BIT, @Cur_IsUnique BIT, @Cur_IdxType TINYINT
    DECLARE @Helper_IdxCols NVARCHAR(MAX), @Helper_IncCols NVARCHAR(MAX)

    -- =============================================
    -- 2. DEFINE NEW STRUCTURE
    -- =============================================
    DECLARE @ColumnDefs TABLE (
        ID INT IDENTITY(1,1),
        ColumnName NVARCHAR(128),
        DataType NVARCHAR(100),
        IsNullable CHAR(1), -- 'Y' = NULL, 'N' = NOT NULL
        Description NVARCHAR(4000)
    );

    -- Insert Columns
    INSERT INTO @ColumnDefs (ColumnName, DataType, IsNullable, Description)
    VALUES
        ('ID', 'BIGINT IDENTITY(1,1)', 'N', 'Primary Key'),
        ('post_author', 'BIGINT', 'N', 'Author ID'),
        ('post_date', 'DATETIME2(0)', 'N', 'Post Date'),
        ('post_date_gmt', 'DATETIME2(0)', 'N', 'Post Date GMT'),
        ('post_content', 'NVARCHAR(MAX)', 'N', 'Post Content'),
        ('post_title', 'NVARCHAR(MAX)', 'N', 'Post Title'),
        ('post_excerpt', 'NVARCHAR(MAX)', 'N', 'Post Excerpt'),
        ('post_status', 'NVARCHAR(20)', 'N', 'Post Status (publish, draft, etc.)'),
        ('comment_status', 'NVARCHAR(20)', 'N', 'Comment Status (open/closed)'),
        ('ping_status', 'NVARCHAR(20)', 'N', 'Ping Status'),
        ('post_password', 'NVARCHAR(255)', 'N', 'Post Password'),
        ('post_name', 'NVARCHAR(200)', 'N', 'Post Name (Slug)'),
        ('to_ping', 'NVARCHAR(MAX)', 'N', 'URLs to ping'),
        ('pinged', 'NVARCHAR(MAX)', 'N', 'Pinged URLs'),
        ('post_modified', 'DATETIME2(0)', 'N', 'Modification Date'),
        ('post_modified_gmt', 'DATETIME2(0)', 'N', 'Modification Date GMT'),
        ('post_content_filtered', 'NVARCHAR(MAX)', 'N', 'Filtered Content'),
        ('post_parent', 'BIGINT', 'N', 'Parent Post ID'),
        ('guid', 'NVARCHAR(255)', 'N', 'Global Unique ID'),
        ('menu_order', 'INT', 'N', 'Menu Order'),
        ('post_type', 'NVARCHAR(20)', 'N', 'Post Type (post, page, etc.)'),
        ('post_mime_type', 'NVARCHAR(100)', 'N', 'Mime Type'),
        ('comment_count', 'BIGINT', 'N', 'Comment Count');
  
    -- =============================================
    -- 2.1 AUTO-DISCOVERY & IDENTITY CHECK
    -- =============================================

    -- Detect Identity Column Name in New Structure
    SELECT TOP 1 @IdentityColName = ColumnName FROM @ColumnDefs WHERE DataType LIKE '%IDENTITY%';

    IF @TargetObjID IS NOT NULL
    BEGIN
        PRINT '... Auto-detecting missing columns from existing table ...'
        INSERT INTO @ColumnDefs (ColumnName, DataType, IsNullable, Description)
        SELECT
            c.name,
            CASE
                WHEN t.name IN ('varchar', 'char', 'varbinary', 'binary') THEN
                    t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')'
                WHEN t.name IN ('nvarchar', 'nchar') THEN
                    t.name + '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR) END + ')'
                WHEN t.name IN ('decimal', 'numeric') THEN
                    t.name + '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
                ELSE
                    t.name
            END,
            CASE WHEN c.is_nullable = 1 THEN 'Y' ELSE 'N' END,
            ISNULL(CAST(ep.value AS NVARCHAR(4000)), '')
        FROM sys.columns c
        JOIN sys.types t ON c.user_type_id = t.user_type_id
        LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
        WHERE c.object_id = @TargetObjID
          AND c.is_computed = 0
          AND c.name NOT IN (SELECT ColumnName FROM @ColumnDefs)
        ORDER BY c.column_id;
        PRINT '   -> Auto-discovery complete.'
    END

    -- =============================================
    -- 3. BACKUP RELATIONS
    -- =============================================
    IF @TargetObjID IS NOT NULL
    BEGIN
        PRINT '... Backing up Relations/Indexes ...'
        -- FKs
        DECLARE FKCursor CURSOR LOCAL FAST_FORWARD FOR SELECT fk.object_id, fk.name, SCHEMA_NAME(fk.schema_id), OBJECT_NAME(fk.parent_object_id) FROM sys.foreign_keys fk WHERE fk.referenced_object_id = @TargetObjID;
        OPEN FKCursor; FETCH NEXT FROM FKCursor INTO @Cur_FKID, @Cur_FKName, @Cur_ParentSchema, @Cur_ParentTable;
        WHILE @@FETCH_STATUS = 0 BEGIN
            SELECT @Helper_FKCols = STUFF((SELECT ',' + QUOTENAME(c.name) FROM sys.foreign_key_columns fkc JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id WHERE fkc.constraint_object_id = @Cur_FKID ORDER BY fkc.constraint_column_id FOR XML PATH('')), 1, 1, '');
            SELECT @Helper_RefCols = STUFF((SELECT ',' + QUOTENAME(c.name) FROM sys.foreign_key_columns fkc JOIN sys.columns c ON fkc.referenced_object_id = c.object_id AND fkc.referenced_column_id = c.column_id WHERE fkc.constraint_object_id = @Cur_FKID ORDER BY fkc.constraint_column_id FOR XML PATH('')), 1, 1, '');
            SET @SqlDropFK += 'ALTER TABLE ' + QUOTENAME(@Cur_ParentSchema) + '.' + QUOTENAME(@Cur_ParentTable) + ' DROP CONSTRAINT ' + QUOTENAME(@Cur_FKName) + ';' + CHAR(13);
            SET @SqlCreateFK += 'ALTER TABLE ' + QUOTENAME(@Cur_ParentSchema) + '.' + QUOTENAME(@Cur_ParentTable) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(@Cur_FKName) + ' FOREIGN KEY (' + @Helper_FKCols + ') REFERENCES ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' (' + @Helper_RefCols + ');' + CHAR(13);
            FETCH NEXT FROM FKCursor INTO @Cur_FKID, @Cur_FKName, @Cur_ParentSchema, @Cur_ParentTable;
        END
        CLOSE FKCursor; DEALLOCATE FKCursor;

        -- Indexes
        DECLARE IdxCursor CURSOR LOCAL FAST_FORWARD FOR SELECT index_id, name, is_primary_key, is_unique, type FROM sys.indexes WHERE object_id = @TargetObjID AND type IN (1, 2) ORDER BY is_primary_key DESC;
        OPEN IdxCursor; FETCH NEXT FROM IdxCursor INTO @Cur_IdxID, @Cur_IdxName, @Cur_IsPK, @Cur_IsUnique, @Cur_IdxType;
        WHILE @@FETCH_STATUS = 0 BEGIN
            SELECT @Helper_IdxCols = STUFF((SELECT ',' + QUOTENAME(c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = @TargetObjID AND ic.index_id = @Cur_IdxID AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH('')), 1, 1, '');
            SELECT @Helper_IncCols = STUFF((SELECT ',' + QUOTENAME(c.name) FROM sys.index_columns ic JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id WHERE ic.object_id = @TargetObjID AND ic.index_id = @Cur_IdxID AND ic.is_included_column = 1 ORDER BY ic.column_id FOR XML PATH('')), 1, 1, '');
            IF @Cur_IsPK = 1 SET @SqlCreatePK_Indexes += 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@Cur_IdxName) + ' PRIMARY KEY ' + CASE WHEN @Cur_IdxType = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' (' + @Helper_IdxCols + ');' + CHAR(13);
            ELSE SET @SqlCreatePK_Indexes += 'CREATE ' + CASE WHEN @Cur_IsUnique = 1 THEN 'UNIQUE ' ELSE '' END + 'NONCLUSTERED INDEX ' + QUOTENAME(@Cur_IdxName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' (' + @Helper_IdxCols + ')' + CASE WHEN LEN(@Helper_IncCols) > 0 THEN ' INCLUDE (' + @Helper_IncCols + ')' ELSE '' END + ';' + CHAR(13);
            FETCH NEXT FROM IdxCursor INTO @Cur_IdxID, @Cur_IdxName, @Cur_IsPK, @Cur_IsUnique, @Cur_IdxType;
        END
        CLOSE IdxCursor; DEALLOCATE IdxCursor;
    END

    -- =============================================
    -- 4. BUILD SCRIPTS & CHECK SHARED IDENTITY
    -- =============================================
    DECLARE ScriptCursor CURSOR LOCAL FAST_FORWARD FOR SELECT ColumnName, DataType, IsNullable FROM @ColumnDefs ORDER BY ID;
    OPEN ScriptCursor;
    FETCH NEXT FROM ScriptCursor INTO @Cur_ColName, @Cur_DataType, @Cur_IsNullable;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Build Create List
        IF LEN(@ColList) > 0 SET @ColList += ', ' + CHAR(13) + CHAR(10) + '    ';
        SET @ColList += QUOTENAME(@Cur_ColName) + ' ' + @Cur_DataType + CASE WHEN @Cur_IsNullable = 'Y' THEN ' NULL' ELSE ' NOT NULL' END;

        -- Build Shared Column List
        IF @TargetObjID IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = @TargetObjID AND name = @Cur_ColName)
        BEGIN
            IF LEN(@SharedColList) > 0 SET @SharedColList += ', ';
            SET @SharedColList += QUOTENAME(@Cur_ColName);

            -- [KEY FIX] If this shared column is the Identity Column, mark flag to Preserve Identity
            IF @IdentityColName IS NOT NULL AND @Cur_ColName = @IdentityColName
            BEGIN
                SET @PreserveIdentity = 1;
            END
        END

        FETCH NEXT FROM ScriptCursor INTO @Cur_ColName, @Cur_DataType, @Cur_IsNullable;
    END
    CLOSE ScriptCursor; DEALLOCATE ScriptCursor;

    -- =============================================
    -- 5. EXECUTION
    -- =============================================

    -- 5.1 Create Temp Table
    SET @CreateTableSQL = 'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TempTableName) + ' (' + @ColList + ');';
    PRINT 'Creating Temp Table...'
    EXEC sp_executesql @CreateTableSQL;

    -- 5.2 Copy Data
    IF @TargetObjID IS NOT NULL AND LEN(@SharedColList) > 0
    BEGIN
        IF LEN(@SqlDropFK) > 0 EXEC sp_executesql @SqlDropFK;

        PRINT 'Copying data...'
        SET @CopySQL = '';

        -- Only set IDENTITY_INSERT ON if we are actually copying the Identity Column
        IF @PreserveIdentity = 1
        BEGIN
             SET @CopySQL += 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TempTableName) + ' ON; ';
        END

        SET @CopySQL += 'INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TempTableName) + ' (' + @SharedColList + ') ' +
                        'SELECT ' + @SharedColList + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '; ';

        IF @PreserveIdentity = 1
        BEGIN
             SET @CopySQL += 'SET IDENTITY_INSERT ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TempTableName) + ' OFF; ';
        END

        EXEC sp_executesql @CopySQL;
    END

    -- 5.3 Column Descriptions
    DECLARE DescCursor CURSOR LOCAL FAST_FORWARD FOR SELECT ColumnName, Description FROM @ColumnDefs WHERE Description IS NOT NULL AND Description <> '';
    OPEN DescCursor;
    FETCH NEXT FROM DescCursor INTO @Cur_ColName, @Cur_Desc;
    WHILE @@FETCH_STATUS = 0 BEGIN
        EXEC sp_addextendedproperty @name = N'MS_Description', @value = @Cur_Desc, @level0type = N'SCHEMA', @level0name = @SchemaName, @level1type = N'TABLE',  @level1name = @TempTableName, @level2type = N'COLUMN', @level2name = @Cur_ColName;
        FETCH NEXT FROM DescCursor INTO @Cur_ColName, @Cur_Desc;
    END
    CLOSE DescCursor; DEALLOCATE DescCursor;

    -- 5.4 Table Description
    PRINT 'Adding Table Description...'
    EXEC sp_addextendedproperty @name = N'MS_Description', @value = @TableDesc, @level0type = N'SCHEMA', @level0name = @SchemaName, @level1type = N'TABLE',  @level1name = @TempTableName;

    -- 5.5 Swap Tables
    IF @TargetObjID IS NOT NULL
    BEGIN
        PRINT 'Swapping tables...'
        SET @CurrentFullName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
        SET @TempFullName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TempTableName);

        EXEC sp_rename @CurrentFullName, @OldTableName;
        EXEC sp_rename @TempFullName, @TableName;

        SET @DropOldSQL = 'DROP TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@OldTableName) + ';';
        EXEC sp_executesql @DropOldSQL;

        IF LEN(@SqlCreatePK_Indexes) > 0 EXEC sp_executesql @SqlCreatePK_Indexes;
        IF LEN(@SqlCreateFK) > 0 EXEC sp_executesql @SqlCreateFK;
    END
    ELSE
    BEGIN
        SET @TempFullName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TempTableName);
        EXEC sp_rename @TempFullName, @TableName;
    END

COMMIT TRANSACTION
PRINT 'Migration Completed Successfully.'

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    SET @Msg = 'ERROR: ' + ERROR_MESSAGE() + ' (Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)) + ')';
    PRINT @Msg;
END CATCH;