SQL server跨服务器更同库同名表增加对应字段

配置Linked Server：在第一台服务器上配置连接到第二台服务器的Linked Server，以便跨服务器查询。

定期检查字段差异：使用SQL Server Agent作业定期执行脚本，检查第二台服务器上的表字段。

生成并执行ALTER语句：动态生成并执行ALTER TABLE语句，添加缺失字段，处理数据类型、NULL约束和默认值。

创建同步脚本

以下脚本检查并同步字段，需在第一台服务器的pubdata库中执行：

DECLARE @TableName NVARCHAR(128), @ColumnName NVARCHAR(128);
DECLARE @DataType NVARCHAR(128), @IsNullable NVARCHAR(3), @ColumnDefault NVARCHAR(MAX);
DECLARE @CharMaxLength INT, @NumericPrecision INT, @NumericScale INT;
DECLARE @Sql NVARCHAR(MAX);

— 遍历第二台服务器上的所有表
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME
FROM [Server2].[pubdata].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
— 检查第一台是否存在该表
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableName AND TABLE_CATALOG = 'pubdata')
BEGIN
— 获取第二台表的所有字段详情
DECLARE ColumnCursor CURSOR FOR
SELECT
c.COLUMN_NAME,
c.DATA_TYPE,
c.CHARACTER_MAXIMUM_LENGTH,
c.NUMERIC_PRECISION,
c.NUMERIC_SCALE,
c.IS_NULLABLE,
c.COLUMN_DEFAULT
FROM [Server2].[pubdata].INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = @TableName;

OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType, @CharMaxLength, @NumericPrecision, @NumericScale, @IsNullable, @ColumnDefault;

WHILE @@FETCH_STATUS = 0
BEGIN
— 检查第一台是否存在该字段
IF NOT EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME = @ColumnName
AND TABLE_CATALOG = 'pubdata'
)
BEGIN
— 构造数据类型（处理长度、精度等）
DECLARE @DataTypeFull NVARCHAR(128) = @DataType;
IF @DataType IN ('varchar', 'char', 'nvarchar', 'nchar', 'binary', 'varbinary')
SET @DataTypeFull = @DataType + '(' + CASE WHEN @CharMaxLength = -1 THEN 'MAX' ELSE CAST(@CharMaxLength AS NVARCHAR) END + ')';
ELSE IF @DataType IN ('decimal', 'numeric')
SET @DataTypeFull = @DataType + '(' + CAST(@NumericPrecision AS NVARCHAR) + ',' + CAST(@NumericScale AS NVARCHAR) + ')';
ELSE IF @DataType IN ('datetime2', 'datetimeoffset', 'time')
SET @DataTypeFull = @DataType + '(' + CAST(@NumericScale AS NVARCHAR) + ')';

— 处理默认值（去除括号）
DECLARE @CleanDefault NVARCHAR(MAX) = NULL;
IF @ColumnDefault IS NOT NULL
BEGIN
SET @CleanDefault = SUBSTRING(@ColumnDefault, 2, LEN(@ColumnDefault) – 2);
— 字符串类型添加引号
IF @DataType IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
SET @CleanDefault = '''' + REPLACE(@CleanDefault, '''', '''''') + '''';
END

— 生成ALTER语句
SET @Sql = 'ALTER TABLE ' + QUOTENAME(@TableName) + ' ADD ' + QUOTENAME(@ColumnName) + ' ' + @DataTypeFull;
IF @IsNullable = 'NO'
SET @Sql += ' NOT NULL';
ELSE
SET @Sql += ' NULL';

IF @CleanDefault IS NOT NULL
BEGIN
DECLARE @DefaultName NVARCHAR(128) = 'DF_' + @TableName + '_' + @ColumnName;
SET @Sql += ' CONSTRAINT ' + QUOTENAME(@DefaultName) + ' DEFAULT ' + @CleanDefault;
END

— 执行并捕获错误
BEGIN TRY
EXEC sp_executesql @Sql;
PRINT '成功添加字段: ' + @ColumnName + ' 到表 ' + @TableName;
END TRY
BEGIN CATCH
PRINT '错误: 添加字段 ' + @ColumnName + ' 失败 – ' + ERROR_MESSAGE();
END CATCH
END

FETCH NEXT FROM ColumnCursor INTO @ColumnName, @DataType, @CharMaxLength, @NumericPrecision, @NumericScale, @IsNullable, @ColumnDefault;
END

CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
END

FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;