RT
转自 Sql Server中同步两个数据库表的数据结构_sqlserver同步表结构-CSDN博客
------------------------------------------------------------------------------ --功能：源数据库和本地目标数据库中同步表,如有增加的列,在此备库中也自动增加列 --##UserTables_Source ： 存放来源数据库用户表和表中字段 --##UserTables ： 存放本地数据库用户表和表中字段 --##UserTables_Tab ： 在来源数据库存在的（用户表）而本地不存在，则执行建新表，只建表结构 --##UserTables_Col ： 在来源数据库存在的（字段）而本地不存在，则执行添加字段 --需一键替换数据数据库名称"[源数据库名称]"改为源数据库 --created by lilijie ---------------------------------------------------------------------------------------- --'bit','datetime','int','real','uniqueidentifier','timestamp','smallint','float' declare @TableName nvarchar(200) declare @PkCol nvarchar(50) declare @PkName nvarchar(200) declare @PkCols nvarchar(max) declare @PkColsDrop nvarchar(max) --if not exists (select * from sys.servers where is_linked=1 and name='连接服务器名') --BEGIN -- EXEC sp_addlinkedserver @server = N'连接服务器名', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'192.168.2.23', @catalog=N'' -- EXEC sp_addlinkedsrvlogin @rmtsrvname=N'连接服务器名',@useself=N'False',@locallogin='sa',@rmtuser='sa',@rmtpassword='sa_password' --END --"[源数据库名称]" 是来源数据库，如来源数据库名为"[源数据库名称]"，需全部替换 --来源数据库用户表和字段的数据类型 IF object_id(N'tempdb..##UserTables_Source',N'U') IS NOT NULL DROP TABLE ##UserTables_Source SELECT a.name AS table_name,b.name AS column_name,c.name AS column_type,CASE WHEN b.length = -1 THEN '(max)' WHEN c.name in ('nvarchar') THEN '('+CAST(b.prec AS VARCHAR ) +')' WHEN c.name in ('decimal') then '('+CAST(b.prec AS VARCHAR ) + ','+ CAST(b.scale AS VARCHAR )+')' ELSE '' END AS column_length,b.colid INTO ##UserTables_Source FROM "[源数据库名称]".sys.sysobjects a,"[源数据库名称]".dbo.syscolumns b ,"[源数据库名称]".dbo.systypes c WHERE a.id = b.id AND b.xtype = c.xusertype AND a.xtype = 'U' ORDER BY a.name,b.colid Lable: --目标数据库用户表(即当前数据库) IF object_id(N'tempdb..##UserTables',N'U') IS NOT NULL DROP TABLE ##UserTables SELECT a.name AS table_name,b.name AS column_name,c.name AS column_type,CASE WHEN b.length = -1 THEN '(max)' WHEN c.name in ('nvarchar') THEN '('+CAST(b.prec AS VARCHAR ) +')' WHEN c.name in ('decimal') then '('+CAST(b.prec AS VARCHAR ) + ','+ CAST(b.scale AS VARCHAR )+')' ELSE '' END AS column_length,b.colid INTO ##UserTables FROM sys.sysobjects a,syscolumns b ,systypes c WHERE a.id = b.id AND b.xtype = c.xusertype AND a.xtype = 'U' ORDER BY a.name,b.colid IF object_id(N'tempdb..##UserTables_Tab ',N'U') IS NOT NULL DROP TABLE ##UserTables_Tab --源库中存在表，而目标库中不存在，则在目标库中添加表 SELECT DISTINCT 'SELECT * INTO '+ table_name + ' FROM "[源数据库名称]".dbo.' + table_name + ' WHERE 1=2' AS create_table,table_name AS TableName INTO ##UserTables_Tab FROM ##UserTables_Source st WHERE NOT EXISTS (SELECT 1 FROM ##UserTables tt WHERE tt.table_name = st.table_name ) --目标库存在表，而源库中不存在，则将目标库中表drop掉 INSERT INTO ##UserTables_Tab(create_table,TableName) SELECT DISTINCT 'DROP TABLE ' + table_name AS create_table,table_name AS TableName FROM ##UserTables tt WHERE NOT EXISTS (SELECT 1 FROM ##UserTables_Source st WHERE tt.table_name = st.table_name) IF EXISTS (SELECT create_table FROM ##UserTables_Tab) BEGIN DECLARE cur_create_table CURSOR FOR SELECT create_table,TableName FROM ##UserTables_Tab DECLARE @create_table nvarchar(max) OPEN cur_create_table FETCH NEXT FROM cur_create_table INTO @create_table,@TableName WHILE @@FETCH_STATUS = 0 BEGIN print @create_table EXEC(@create_table)--执行建表或drop表的动作 FETCH NEXT FROM cur_create_table INTO @create_table,@TableName END CLOSE cur_create_table DEALLOCATE cur_create_table GOTO Lable END --目标数据库用户表中字段名称和本地表中字段名称要一致 IF object_id(N'tempdb..##UserTables_Col ',N'U') IS NOT NULL BEGIN DROP TABLE ##UserTables_Col GOTO Lable END SELECT DISTINCT 'ALTER TABLE '+ table_name + ' ADD ' + column_name +' '+ column_type + column_length AS alter_column INTO ##UserTables_Col FROM ##UserTables_Source st WHERE NOT EXISTS (SELECT 1 FROM ##UserTables tt WHERE tt.table_name = st.table_name AND tt.column_name = st.column_name ) --来源表中字段和本地表中字段数据类型和长度要一致 INSERT INTO ##UserTables_Col(alter_column ) SELECT DISTINCT 'ALTER TABLE '+ table_name + ' ALTER COLUMN ' + column_name +' '+ column_type + column_length AS add_column FROM ##UserTables_Source st WHERE NOT EXISTS (SELECT 1 FROM ##UserTables tt WHERE tt.table_name = st.table_name AND tt.column_name = st.column_name and tt.column_type = st.column_type and tt.column_length = st.column_length ) --select * from ##UserTables_Col --来源表中字段不存在，而本地表中字段存在，则删除本地表中字段 INSERT INTO ##UserTables_Col(alter_column ) SELECT 'ALTER TABLE '+table_name + ' DROP COLUMN ' + column_name FROM ##UserTables tt WHERE NOT EXISTS (SELECT 1 FROM ##UserTables_Source st WHERE tt.table_name = st.table_name AND tt.column_name = st.column_name ) DECLARE @alter_column nvarchar(max) IF EXISTS (SELECT alter_column FROM ##UserTables_Col) BEGIN DECLARE cur_alter_column CURSOR FOR SELECT alter_column FROM ##UserTables_Col ORDER BY 1 ASC OPEN cur_alter_column FETCH NEXT FROM cur_alter_column INTO @alter_column WHILE @@FETCH_STATUS = 0 BEGIN print @alter_column EXEC(@alter_column)--执行添加字段的动作 FETCH NEXT FROM cur_alter_column INTO @alter_column END CLOSE cur_alter_column DEALLOCATE cur_alter_column END IF object_id(N'tempdb..##UserTables_SourcePK ',N'U') IS NOT NULL DROP TABLE ##UserTables_SourcePK select tb.name TableName,t1.name PkCol ,t2.name PkName -- 源库中表 主键 into ##UserTables_SourcePK from "[源数据库名称]".dbo.SYSCOLUMNS t1 left join "[源数据库名称]".dbo.SYSOBJECTS t2 on t2.parent_obj = t1.id AND t2.xtype = 'PK' left join "[源数据库名称]".dbo.SYSINDEXES t3 on t3.id = t1.id and t2.name = t3.name left join "[源数据库名称]".dbo.SYSINDEXKEYS t4 on t1.colid = t4.colid and t4.id = t1.id and t4.indid = t3.indid left join "[源数据库名称]".dbo.systypes t5 on t1.xtype=t5.xtype left join sys.extended_properties t6 on t1.id=t6.major_id and t1.colid=t6.minor_id left join "[源数据库名称]".dbo.SYSOBJECTS tb on tb.id=t1.id and t5.name<>'sysname' and t4.id is not null where tb.name in( select table_name from ##UserTables_Source) IF object_id(N'tempdb..##UserTablesPK ',N'U') IS NOT NULL DROP TABLE ##UserTablesPK select tb.name TableName,t1.name PkCol ,t2.name PkName --目标库中表 主键 into ##UserTablesPK from SYSCOLUMNS t1 left join SYSOBJECTS t2 on t2.parent_obj = t1.id AND t2.xtype = 'PK' left join SYSINDEXES t3 on t3.id = t1.id and t2.name = t3.name left join SYSINDEXKEYS t4 on t1.colid = t4.colid and t4.id = t1.id and t4.indid = t3.indid left join systypes t5 on t1.xtype=t5.xtype left join sys.extended_properties t6 on t1.id=t6.major_id and t1.colid=t6.minor_id left join SYSOBJECTS tb on tb.id=t1.id and t5.name<>'sysname' and t4.id is not null where tb.name in( select table_name from ##UserTables) DECLARE CurPk cursor for (select DISTINCT TableName from ##UserTables_SourcePK a where not exists (select 1 from ##UserTablesPK b where a.TableName = b.TableName and a.PkCol = b.PkCol and a.PkName = b.PkName ) ) OPEN CurPk FETCH NEXT FROM CurPk INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @PkCols= '' DECLARE CurPkDet CURSOR FOR (SELECT PkCol,PkName FROM ##UserTables_SourcePK WHERE TableName = @TableName ) OPEN CurPkDet FETCH NEXT FROM CurPkDet INTO @PkCol,@PkName WHILE @@FETCH_STATUS = 0 BEGIN SET @PkCols = @PkCols + ',' + @PkCol FETCH NEXT FROM CurPkDet INTO @PkCol,@PkName END --DECLARE @PkColsDrop NVARCHAR(MAX) = '' SET @PkColsDrop = ( SELECT DISTINCT 'ALTER TABLE '+ TableName + ' DROP CONSTRAINT ' + PkName AS PkColsDrop FROM ##UserTablesPK WHERE TableName = @TableName ) IF @PkColsDrop IS NOT NULL print @PkColsDrop EXEC (@PkColsDrop) --删除旧的主键 SET @PkCols = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT [' + @PkName + '] PRIMARY KEY NONCLUSTERED ( ' + STUFF(@PkCols,1,1,'') + ')' print @PkCols EXEC(@PkCols) --SELECT @PkCols CLOSE CurPkDet DEALLOCATE CurPkDet FETCH NEXT FROM CurPk INTO @TableName END CLOSE CurPk DEALLOCATE CurPk DROP TABLE ##UserTables_Source DROP TABLE ##UserTables DROP TABLE ##UserTables_Tab DROP TABLE ##UserTables_Col DROP TABLE ##UserTables_SourcePK DROP TABLE ##UserTablesPK