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;
