Sql Server中同步两个数据库表的数据结构-CSDN博客

本文链接：https://blog.csdn.net/lilijie0404/article/details/108645886
Sql Server中同步两个数据库表的数据结构
库A中用户建立的表结构和库B中表结构是否一样，不一样的话，自动同步。
CREATE PROCEDURE [dbo].[up_SynchroTabCol]
AS
------------------------------------------------------------------------------
--功能：源数据库和本地目标数据库中同步表,如有增加的列,在此备库中也自动增加列
--##UserTables_Source	： 存放来源数据库用户表和表中字段
--##UserTables			： 存放本地数据库用户表和表中字段
--##UserTables_Tab		： 在来源数据库存在的（用户表）而本地不存在，则执行建新表，只建表结构
--##UserTables_Col		： 在来源数据库存在的（字段）而本地不存在，则执行添加字段
--需一键替换数据数据库名称[源数据库名]改为源数据库
--created by lilijie
----------------------------------------------------------------------------------------
--'bit','datetime','int','real','uniqueidentifier','timestamp','smallint','float' 
BEGIN	
		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 
						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 
						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 
				EXEC (@PkColsDrop)  --删除旧的主键

			SET @PkCols = 'ALTER TABLE ' + @TableName  + ' ADD  CONSTRAINT [' + @PkName + '] PRIMARY KEY NONCLUSTERED ( ' + STUFF(@PkCols,1,1,'')  + ')'
			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
	END
原创作品，如果对你有用，请记得打赏姐姐。