Compare actual table vs. archive table

We have seen many times that we have altered actual table column but missed to change in archival table. It will fail our archival process to work. Archival is really important to application or it will increase load to database. Purging is used to cleanup unwanted data from database to free space on server.

I tried to create script which used to compare actual table vs. archival table also create backup and release script for same.

SELECT 
    MT.name                    'Table Name',
    MTC.name                'Column Name',
    MTCT.name                'Column Type Name',
    MTC.max_length            'Column max_length',
    MTC.precision            'Column precision',
    AT.name                    'Archive Table Name',
    ATCT.name                'Archive Column Type Name',
    ATC.max_length            'Archive Column max_length',
    ATC.precision            'Archive precision',
    'ALTER TABLE ' + SCHEMA_NAME(AT.schema_id) + '.' +  AT.name + ' ALTER COLUMN ' + ATC.name + ' ' + ATCT.name +
    CASE 
        WHEN ATCT.name IN ('char','varchar','nchar','nvarchar') AND ATC.max_length = -1 THEN '(max)'
        WHEN ATCT.name IN ('char','varchar') THEN '('+ CAST(ATC.max_length AS VARCHAR) +')'
        WHEN ATCT.name IN ('nchar','nvarchar') THEN '('+ CAST(ATC.max_length/2 AS VARCHAR) +')'
        WHEN ATCT.name IN ('decimal','numeric') THEN '('+ CAST(ATC.precision AS VARCHAR) +','+ CAST(ATC.scale AS VARCHAR) +')'
        ELSE ''
    END 'Backup Script',
    'ALTER TABLE ' + SCHEMA_NAME(MT.schema_id) + '.' +  AT.name + ' ALTER COLUMN ' + MTC.name + ' ' + MTCT.name +
    CASE 
        WHEN MTCT.name IN ('char','varchar','nchar','nvarchar') AND MTC.max_length = -1 THEN '(max)'
        WHEN MTCT.name IN ('char','varchar') THEN '('+ CAST(MTC.max_length AS VARCHAR) +')'
        WHEN MTCT.name IN ('nchar','nvarchar') THEN '('+ CAST(MTC.max_length/2 AS VARCHAR) +')'
        WHEN MTCT.name IN ('decimal','numeric') THEN '('+ CAST(MTC.precision AS VARCHAR) +','+ CAST(MTC.scale AS VARCHAR) +')'
        ELSE ''
    END 'Release Script'
FROM 
    sys.objects MT 
    INNER JOIN sys.objects AT ON MT.name + '_Archive' = AT.name
    INNER JOIN sys.columns MTC ON MTC.object_id = MT.object_id
    INNER JOIN sys.types MTCT ON MTCT.user_type_id = MTC.user_type_id
    INNER JOIN sys.columns ATC ON ATC.object_id = AT.object_id
        AND MTC.name = ATC.name
        AND 
            (
                MTC.system_type_id <> ATC.system_type_id
                OR MTC.user_type_id <> ATC.user_type_id
                OR MTC.max_length <> ATC.max_length
                OR MTC.precision <> ATC.precision
                OR MTC.scale <> ATC.scale
            )
    INNER JOIN sys.types ATCT ON ATCT.user_type_id = ATC.user_type_id
WHERE 
    MT.type = 'U' 
    AND AT.type = 'U'
ORDER BY 
    MT.name,
    AT.name