SP for maintaining same table structure for Main and archive table.

GO

/****** Object:  StoredProcedure [dbo].[USP_ManageArchivalTableStrucutures]    Script Date: 2/2/2016 11:00:02 AM ******/

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

-- =============================================
-- Author:           Gokuldas Palapatta
-- Create date:  2016-02-01 12:32:18.960
-- Description:      Help_archiveTables
--
--Table Details
-- CREATE TABLE [dbo].[Archive_Setting](
-- [Id] [int] IDENTITY(1,1) NOT NULL,
-- [SnapShotTable] [varchar](100) NOT NULL,
-- [ArchiveTable] [varchar](100) NOT NULL)
-- =============================================
ALTER PROCEDURE [dbo].[USP_ManageArchivalTableStrucutures]
-- Add the parameters for the stored procedure here
AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
        --Declaring All variables


        DECLARE @Query VARCHAR(MAX) = '';
        IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
            BEGIN
                DROP TABLE #temp1;
            END;
            IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
                BEGIN
                    DROP TABLE #temp2;
                END;
                IF OBJECT_ID('tempdb..#temp3') IS NOT NULL
                    BEGIN
                        DROP TABLE #temp3;
                    END;

                    -- pushing all the details to temptable
                    SELECT C2.*,
                           C1.* INTO #TEMP1
                    FROM INFORMATION_SCHEMA.COLUMNS C2
                         JOIN ARCHIVE_SETTING C1 ON C2.TABLE_NAME = C1.SNAPSHOTTABLE
                                                 OR C2.TABLE_NAME = C1.ARCHIVETABLE;


                    -- Filtering only changed and new coloumns
                    SELECT * INTO #TEMP2
                    FROM(
                        SELECT DISTINCT COLUMN_NAME,
                                        C2.DATA_TYPE,
                                        C2.CHARACTER_MAXIMUM_LENGTH,
                                        C2.TABLE_NAME,
                                        0 AS ISCHANGED,
                                        1 AS NEW,
                                        ARCHIVETABLE AS ALTERTABLE,
                                        C2.IS_NULLABLE
                        FROM #TEMP1 C2
                        WHERE TABLE_NAME = SNAPSHOTTABLE
                          AND C2.COLUMN_NAME NOT IN(
                                 SELECT COLUMN_NAME
                                 FROM #TEMP1
                                 WHERE TABLE_NAME = ARCHIVETABLE )
                        UNION ALL
                        SELECT DISTINCT C2.COLUMN_NAME,
                                        C2.DATA_TYPE,
                                        C2.CHARACTER_MAXIMUM_LENGTH,
                                        C2.TABLE_NAME,
                                        1 AS ISCHANGED,
                                        0 AS NEW,
                                        ARCHIVETABLE AS ALTERTABLE,
                                        C2.IS_NULLABLE
                        FROM #TEMP1 C2
                        WHERE TABLE_NAME = SNAPSHOTTABLE
                          AND C2.COLUMN_NAME IN(
                                 SELECT COLUMN_NAME
                                 FROM #TEMP1 C1
                                 WHERE TABLE_NAME = ARCHIVETABLE
                                   AND C2.COLUMN_NAME = C1.COLUMN_NAME
                                   AND ( C2.IS_NULLABLE <> C1.IS_NULLABLE
                                      OR C1.DATA_TYPE <> C2.DATA_TYPE
                                      OR C1.CHARACTER_MAXIMUM_LENGTH <> C2.CHARACTER_MAXIMUM_LENGTH
                                       ))) AS CT;

                    -- preparing Query based on the changes
                    SELECT * INTO #Temp3
                    FROM(
                        SELECT *,
                               CASE
                                   WHEN data_type = 'varchar'
                                     OR data_type = 'nvarchar'
                                     OR data_type = 'char'
                                   THEN 'ALTER TABLE ' + AlterTable + '
ALTER COLUMN ' + COLUMN_NAME + ' ' + data_type + ' (' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ' )' + ' ' + CASE
                                                                                                                         WHEN IS_NULLABLE = 'YES'
                                                                                                                         THEN 'null'
                                                                                                                         ELSE 'Not null'
                                                                                                                     END
                                   ELSE 'ALTER TABLE ' + AlterTable + '
ALTER COLUMN ' + COLUMN_NAME + ' ' + data_type + ' ' + CASE
                                                           WHEN IS_NULLABLE = 'YES'
                                                           THEN 'null'
                                                           ELSE 'Not null'
                                                       END
                               END AS Query
                        FROM #temp2
                        WHERE IsChanged = 1
                          AND NEW = 0
                        UNION ALL
                        SELECT *,
                               CASE
                                   WHEN data_type = 'varchar'
                                     OR data_type = 'nvarchar'
                                     OR data_type = 'char'
                                   THEN 'ALTER TABLE ' + AlterTable + '
ADD  ' + COLUMN_NAME + ' ' + data_type + ' (' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)) + ' )' + ' ' + CASE
                                                                                                                 WHEN IS_NULLABLE = 'YES'
                                                                                                                 THEN 'null'
                                                                                                                 ELSE 'Not null'
                                                                                                             END
                                   ELSE 'ALTER TABLE ' + AlterTable + '
ADD  ' + COLUMN_NAME + ' ' + data_type + ' ' + CASE
                                                   WHEN IS_NULLABLE = 'YES'
                                                   THEN 'null'
                                                   ELSE 'Not null'
                                               END
                               END AS Query
                        FROM #temp2
                        WHERE IsChanged = 0
                          AND NEW = 1 ) AS CX;

                    -- concardinating  Query based
                    SELECT @Query = COALESCE(@Query + ' ', '') + Query
                    FROM #Temp3;
                    EXEC ( @Query );
    END;