用sql语句实现sqlserver不同库间表相互同步

同步表的方式有很多种因为不想太过于麻烦所以就用存储过程来实现某些表同步

create proc copytbale
as
--表名
declare @name nvarchar(50)
--创建表
declare @createtable    nvarchar(200)
--删除表
declare @deletetable   nvarchar(200)
--定义游标
declare tabname CURSOR FOR  SELECT name FROM  [被复制的库].dbo.SYSOBJECTS WHERE name like'ceshi%'
--开启游标
OPEN tabname 
--向下循环
FETCH NEXT FROM tabname into @name
while @@FETCH_STATUS=0--判断是否提取成功

begin
--判断表是否存在
--IF NOT EXISTS (SELECT name FROM sysobjects WHERE name=@name  AND type = 'U')
--begin
 set  @deletetable='drop table '+@name
 exec(@deletetable)
 set @createtable='select * INTO ' +@name+ ' from [被复制的库].dbo.'+@name
 exec(@createtable)
 --end
 fetch next from tabname into @name 
end
--关闭游标
close tabname
--释放游标
deallocate tabname