Stored procedure to swap tables via RENAME and DROP TABLE and transfer any sensitivity classifications from the original table to the new table

SQLDWTableSwapWithClassifications

CREATE PROC SwapWithMetadata
    @SrcSchema NVARCHAR(128),
    @SrcTable NVARCHAR(128),
    @DestSchema NVARCHAR(128),
    @DestTable NVARCHAR(128),
    @TransferMetadata BIT,
    @DropOldTable BIT
AS

BEGIN
    SET NOCOUNT ON

    BEGIN TRY 
        --Check if destination table exists
        DECLARE @DestSchemaQualifiedTableName NVARCHAR(257)
        SET @DestSchemaQualifiedTableName = @DestSchema + '.' + @DestTable
        
        IF OBJECT_ID(@DestSchemaQualifiedTableName) IS NULL
        BEGIN
        DECLARE @DestErr NVARCHAR(MAX)
            SET @DestErr = 'Table ' + @DestSchemaQualifiedTableName + ' not found'
            RAISERROR(@DestErr, 15, 1)
        END

        --Check if source table exists
        DECLARE @SrcSchemaQualifiedTableName NVARCHAR(257)
        SET @SrcSchemaQualifiedTableName = @SrcSchema + '.' + @SrcTable

        IF OBJECT_ID(@SrcSchemaQualifiedTableName) IS NULL
            BEGIN
                DECLARE @SrcErr NVARCHAR(MAX)
                SET @SrcErr = 'Table ' + @SrcSchemaQualifiedTableName + ' not found'
                RAISERROR(@SrcErr, 15, 1)
            END
				
        --Move destination table to destination_old. Move source table to destination
        DECLARE @RenameSql NVARCHAR(MAX)
        SET @RenameSql = 'RENAME OBJECT ' + @DestSchemaQualifiedTableName + ' TO ' + @DestTable + '_old; '
        Set @RenameSql = @RenameSql + '  RENAME OBJECT ' + @SrcSchemaQualifiedTableName + ' TO ' + @DestTable
        PRINT 'Executing ' + @RenameSql + ' ...'
        EXEC sp_executesql @RenameSql;

        --drop temp table if it exists
        IF OBJECT_ID('tempDB..#tempApplySensitivityClassificationsToTable') IS NOT NULL
			DROP TABLE #tempApplySensitivityClassificationsToTable;

        --check if we should transfer data classifications from old to new table 
        IF ISNULL(@TransferMetadata,0) = 1 
		BEGIN

            --put current classifications in a temp table
            DECLARE @OldTable NVARCHAR(128) = @DestTable + '_old';
            WITH  CurrentClassifications as (
                SELECT
                    CAST('dbo' as NVARCHAR(128)) [Schema],
                    CAST(sys.all_objects.name as NVARCHAR(128)) [Table],
                    CAST(sys.all_columns.name as NVARCHAR(128)) [Column],
                    CAST([Information_Type] as NVARCHAR(128)) [Informationtype],
                    CAST([Label] as NVARCHAR(128)) [Label]
                FROM
                    sys.sensitivity_classifications
                    LEFT OUTER JOIN sys.all_objects on sys.sensitivity_classifications.major_id = sys.all_objects.object_id
                    LEFT OUTER JOIN sys.all_columns on sys.sensitivity_classifications.major_id = sys.all_columns.object_id
                        and sys.sensitivity_classifications.minor_id = sys.all_columns.column_id

            )
            SELECT ROW_NUMBER() OVER (ORDER BY [Schema], [Table], [Column]) [ID],
                [Schema], [Table], [Column], [Informationtype], [Label]
            INTO #tempApplySensitivityClassificationsToTable
            FROM CurrentClassifications
            WHERE [Schema] = @DestSchema AND [Table] = @OldTable;


            DECLARE @i INT
            SET @i = 1
            DECLARE @Max INT
            SELECT @Max = COUNT(*)
            FROM #tempApplySensitivityClassificationsToTable;

            PRINT 'Transferring ' + CAST(@Max as VARCHAR(4)) + ' classifications'
            --drop and recreate sensitivity classifications
            DECLARE @Sql NVARCHAR(MAX)
            DECLARE @Col NVARCHAR(128)
            DECLARE @InfoType NVARCHAR(128)
            DECLARE @Label NVARCHAR(128)

        WHILE @i <= @Max
			BEGIN
                SELECT @Col = [Column], @InfoType = [InformationType], @Label = [Label]
                FROM #tempApplySensitivityClassificationsToTable
                WHERE Id = @i

                SET @Sql = 'DROP SENSITIVITY CLASSIFICATION FROM ' + @DestSchemaQualifiedTableName + '.' + @Col
                PRINT 'Executing ' + @Sql + '...'
                EXEC sp_executesql @Sql

                SET @Sql = 'ADD SENSITIVITY CLASSIFICATION TO ' + @DestSchemaQualifiedTableName + '.' + @Col
                IF (@InfoType IS NOT NULL AND @Label IS NOT NULL)
                    BEGIN
                        SET @Sql = @Sql + ' WITH (LABEL=''' + @Label + ''', INFORMATION_TYPE=''' + @InfoType + ''')'
                    END
                ELSE IF (@InfoType IS NOT NULL)
                    BEGIN
                        SET @Sql = @Sql + ' WITH (INFORMATION_TYPE=''' + @InfoType + ''')'
                    END
                ELSE IF (@Label IS NOT NULL)
                    BEGIN
                        SET @Sql = @Sql + ' WITH (LABEL=''' + @InfoType + ''')'
                    END
                ELSE
                    BEGIN
                        SET @Sql = NULL
                    END
                IF (@Sql IS NOT NULL)
                    BEGIN
                        PRINT 'Executing ' + @Sql + '...'
                        EXEC sp_executesql @Sql
                    END

                SET @i = @i + 1
            END
        END

        IF ISNULL(@DropOldTable,0) = 1 
        BEGIN
            DECLARE @DropSql NVARCHAR(MAX)
            SET @DropSql = 'DROP TABLE ' + @DestSchemaQualifiedTableName + '_old;'
            PRINT 'Executing ' + @DropSql + '...'
            EXEC sp_executesql @DropSql;
        END

	END TRY 
	
    BEGIN CATCH 
	    Print 'ERROR... Procedure: ' + ERROR_PROCEDURE() + '  Message: ' + ERROR_MESSAGE()
	END CATCH

END