SP比较两个表的列和数据类型，并根据源表更改目标表

CREATE PROCEDURE CompareTables
AS
BEGIN
    -- 比较两个表的列和数据类型
    IF EXISTS (
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '源表名'
        EXCEPT
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '目标表名'
    )
    BEGIN
        -- 源表存在的列，目标表不存在，执行添加列操作
        DECLARE @ColumnName NVARCHAR(100)
        DECLARE @DataType NVARCHAR(100)
        DECLARE @AddColumnSQL NVARCHAR(MAX)

        DECLARE cur CURSOR FOR
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '源表名'
        EXCEPT
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '目标表名'

        OPEN cur
        FETCH NEXT FROM cur INTO @ColumnName, @DataType

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @AddColumnSQL = 'ALTER TABLE 目标表名 ADD ' + @ColumnName + ' ' + @DataType
            EXEC(@AddColumnSQL)

            FETCH NEXT FROM cur INTO @ColumnName, @DataType
        END

        CLOSE cur
        DEALLOCATE cur
    END

    IF EXISTS (
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '目标表名'
        EXCEPT
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '源表名'
    )
    BEGIN
        -- 目标表存在的列，源表不存在，执行删除列操作
        DECLARE @ColumnName NVARCHAR(100)
        DECLARE @DataType NVARCHAR(100)
        DECLARE @DropColumnSQL NVARCHAR(MAX)

        DECLARE cur CURSOR FOR
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '目标表名'
        EXCEPT
        SELECT COLUMN_NAME, DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = '源表名'

        OPEN cur
        FETCH NEXT FROM cur INTO @ColumnName, @DataType

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @DropColumnSQL = 'ALTER TABLE 目标表名 DROP COLUMN ' + @ColumnName
            EXEC(@DropColumnSQL)

            FETCH NEXT FROM cur INTO @ColumnName, @DataType
        END

        CLOSE cur
        DEALLOCATE cur
    END

    -- 更新目标表的列数据类型
    DECLARE @ColumnName NVARCHAR(100)
    DECLARE @DataType NVARCHAR(100)
    DECLARE @AlterColumnSQL NVARCHAR(MAX)

    DECLARE cur CURSOR FOR
    SELECT c.COLUMN_NAME, c.DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS c
    INNER JOIN INFORMATION_SCHEMA.COLUMNS t ON c.TABLE_NAME = '目标表名' AND t.TABLE_NAME = '源表名' AND c.COLUMN_NAME = t.COLUMN_NAME
    WHERE c.DATA_TYPE <> t.DATA_TYPE

    OPEN cur
    FETCH NEXT FROM cur INTO @ColumnName, @DataType

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @AlterColumnSQL = 'ALTER TABLE 目标表名 ALTER COLUMN ' + @ColumnName + ' ' + @DataType
        EXEC(@AlterColumnSQL)

        FETCH NEXT FROM cur INTO @ColumnName, @DataType
    END

    CLOSE cur
    DEALLOCATE cur
END