--select * from sysobjects where xtype=‘U‘ order by name--数据库中所有的只具有一个主键表添加‘datatsp和datatsp_int‘declare @table_name varchar(50),		@sql varchar(8000),		@col_key varchar(50)		declare cur_tb cursor for	select distinct tablename from v_table_des 	where iskey=‘1‘ and tablename not like ‘%bak‘	group by tablename	having COUNT(*)=1open cur_tbfetch cur_tb into @table_namewhile @@FETCH_STATUS=0begin 	--select * from v_table_des	select @col_key= colname from v_table_des where iskey=‘1‘ and tablename=@table_name	select @sql=‘alter table ‘+@table_name+‘ add datatsp timestamp ‘			+‘alter table ‘+@table_name+‘ add datatsp_int bigint ‘	print @sql				fetch cur_tb into @table_nameendclose cur_tbdeallocate cur_tb

--主数据库--4--将主数据库 数据版本修改为一致declare @table_name varchar(50),		@sql varchar(8000),		@col_key varchar(50)		declare cur_tb cursor for	select distinct tablename from v_table_des 	where iskey=‘1‘ and tablename not like ‘%bak‘	group by tablename	having COUNT(*)=1open cur_tbfetch cur_tb into @table_namewhile @@FETCH_STATUS=0begin 	--select * from v_table_des	select @col_key= colname from v_table_des where iskey=‘1‘ and tablename=@table_name	select @sql=‘update ‘+@table_name+‘ set datatsp_int = convert(bigint,b.datatsp) from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ b where ‘+@table_name+‘.‘+@col_key+‘=b.‘+@col_key+‘	print ‘‘‘+@table_name+‘‘‘	 go ‘		   	print @sql				fetch cur_tb into @table_nameendclose cur_tbdeallocate cur_tb--分数据库declare @i int,		@start bigint,		@end bigint,		@sql varchar(8000),		@dbname varchar(50)--数据库名称				select @i=0,@start=1,@end=50000while @i<=19begin	select @start=1 + @i*50000,			@end=50000+ @i*50000	select @dbname=‘ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)	select @sql=‘	update ‘+@dbname+‘.dbo.T_DataAttachment set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataAttachment b where ‘+@dbname+‘.dbo.T_DataAttachment.PersonID=b.PersonID and ‘+@dbname+‘.dbo.T_DataAttachment.Reason=b.Reason and ‘+@dbname+‘.dbo.T_DataAttachment.Category=b.Category	print ‘‘‘+@dbname+‘.dbo.T_DataAttachment‘‘	go	update ‘+@dbname+‘.dbo.T_DataCertificate set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataCertificate b where ‘+@dbname+‘.dbo.T_DataCertificate.PersonID=b.PersonID 	print ‘‘‘+@dbname+‘.dbo.T_DataCertificate‘‘	go	update ‘+@dbname+‘.dbo.T_DataFingerprint set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataFingerprint b where ‘+@dbname+‘.dbo.T_DataFingerprint.PersonID=b.PersonID and ‘+@dbname+‘.dbo.T_DataFingerprint.Finger=b.Finger	print ‘‘‘+@dbname+‘.dbo.T_DataFingerprint‘‘	go	update ‘+@dbname+‘.dbo.T_DataPhoto set datatsp_int=convert(bigint,b.datatsp) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto b where ‘+@dbname+‘.dbo.T_DataPhoto.PersonID=b.PersonID 	print ‘‘‘+@dbname+‘.dbo.T_DataPhoto‘‘	go‘				print @sql	select @i=@i + 1end

declare @table_name varchar(50),		@sql varchar(8000),		@col_key varchar(50),		@col_name varchar(50),		@sql_upd varchar(8000),		@sql_sel varchar(8000)		declare cur_tb cursor for	select distinct tablename from v_table_des 	where iskey=‘1‘ and tablename not like ‘%bak‘ --and tablename=‘t_person‘	group by tablename	having COUNT(*)=1open cur_tbfetch cur_tb into @table_namewhile @@FETCH_STATUS=0begin 	--select * from v_table_des	select @col_key= colname from v_table_des where iskey=‘1‘ and tablename=@table_name		select @sql_upd=‘‘	select @sql_sel=‘‘	declare cur_ist cursor for--select * from v_table_des where tablename=‘t_person‘ and colname<>‘datatsp_int‘		select colname from v_table_des where tablename=@table_name and colname not in(‘datatsp_int‘,‘datatsp‘)	open cur_ist	fetch cur_ist into @col_name	while @@FETCH_STATUS=0	begin			if @col_name <>@col_key		begin				select @sql_upd=@sql_upd+@table_name+‘.‘+@col_name+‘=bb.‘+@col_name+‘,‘				end		select @sql_sel=@sql_sel+@col_name+‘,‘						fetch cur_ist into @col_name	 	end	close cur_ist	deallocate cur_ist					select @sql=‘	--declare @max_id bigint--订阅数据库最大版本id		--declare @max_id1 bigint--发布数据库最大版本id		--同步主数据库--下列顺序不能打乱--‘+@table_name+‘	select  @max_id=0,@max_id1=0	select @max_id=max(datatsp_int) from ‘+@table_name+‘	 	select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ with(nolock) 	 	--1、删除不存在	delete from ‘+@table_name+‘ where ‘+@col_key+‘ not in(select ‘+@col_key+‘ from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ with(nolock) )		--2、修改	update ‘+@table_name+‘ 	set ‘+@sql_upd+@table_name+‘.datatsp_int=convert(bigint,bb.datatsp)	from 	(		select a.* from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ a with(nolock) where CONVERT(bigint,a.datatsp)>@max_id and CONVERT(bigint,a.datatsp)<=@max_id1	)bb	where ‘+@table_name+‘.‘+@col_key+‘=bb.‘+@col_key+‘	--3、添加	set identity_insert ‘+@table_name+‘ on	insert into ‘+@table_name+‘(‘+@sql_sel+‘datatsp_int)	select ‘+@sql_sel+‘convert(bigint,datatsp) as datatsp_int 	from [192.168.1.163].ylbx.dbo.‘+@table_name+‘ with(nolock) 	where CONVERT(bigint,datatsp)>@max_id and CONVERT(bigint,datatsp)<=@max_id1 and ‘+@col_key+‘ not in(select ‘+@col_key+‘ from ‘+@table_name+‘)	set identity_insert ‘+@table_name+‘ off	‘		print @sql				fetch cur_tb into @table_nameendclose cur_tbdeallocate cur_tb-------------------------------------------------------------------------------------------------------------------------------------------------------------

--执行的结果升级发布数据库和订阅数据库declare @i int,		@start bigint,		@end bigint,		@sql varchar(max),		@dbname varchar(50)--数据库名称				select @i=0,@start=1,@end=50000while @i<=19begin	select @start=1 + @i*50000,			@end=50000+ @i*50000	--select @sql=‘union all SELECT PersonID,Data,OperatorID,OperatorTime,Data_All from ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)+‘..t_dataphoto‘	--select @sql=‘union all SELECT PersonID,Data,Img,Finger,OperatorID,OperatorTime from ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)+‘..T_DataFingerprint‘	--select @sql=‘union all SELECT * from ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)+‘..T_DataCertificate‘	select @dbname=‘ylbx_‘+convert(varchar(8),@start)+‘_‘+convert(varchar(8),@end)	select @sql=‘use ‘+@dbname+‘	go	alter procedure sys_sync_database	as	begin		declare @max_id bigint--订阅数据库最大版本id		    declare @max_id1 bigint--发布数据库最大版本id		    		--T_DataPhoto		select  @max_id=0,@max_id1=0		select @max_id=max(datatsp_int) from T_DataPhoto		select @max_id=min(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto with(nolock) where  CONVERT(bigint,datatsp)>@max_id		select @max_id1=max(CONVERT(bigint,datatsp)) from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto with(nolock)	 		if @max_id1-@max_id>=500		begin			select @max_id1=@max_id +500		end		--1、删除不存在		delete from T_DataPhoto where PersonID not in(select PersonID from [192.168.1.163].‘+@dbname+‘.dbo.T_DataPhoto with(nolock))			--2、修改		update T_DataPhoto 		set T_DataPhoto.Data=http://www.mamicode.com/bb.Data,T_DataPhoto.OperatorID=bb.OperatorID,T_DataPhoto.OperatorTime=bb.OperatorTime,T_DataPhoto.Data_All=bb.Data_All,T_DataPhoto.datatsp_int=convert(bigint,bb.datatsp)>