首页 > 代码库 > 自动生成数据同步存储过程的存储过程

自动生成数据同步存储过程的存储过程

最近几天都在搞数据库,特别是今天,因为需要,自己写了一个自动生成同步数据存储过程的存储过程。。。废话不多少,直接上代码:

 --开始
 --create proc exec_copy
 --as 
 --begin
 --因为同步的表是内网表,基本不做业务逻辑处理,所以所有的表都没有标示列,故我也就没做标示列的处理
 set nocount on
    declare @tab_count int --存放用户表的数量
    declare @col_count int --存放某个表中列的数量
    declare @str_sql varchar(max)--存放动态生成的T-SQL语句
    set @tab_count=(select count(object_id) from sys.objects where type_desc=USER_TABLE)--查询当前数据库中用户表的数量
    --print @tab_count
    declare @jishu_tab int
    set @jishu_tab=1
    declare @object_id int--存放某个表的object_id
    while (@jishu_tab<=@tab_count)--循环数据库中的所有用户表
    begin
        set @object_id=(select top 1 object_id from sys.objects where type_desc=USER_TABLE and name=‘表名)--根据指定表名,如果要同步一个数据库
      --可以注释掉name=‘‘把下面的取消注释
--object_id not in(select top(@jishu_tab-1) object_id from sys.objects where type_desc=‘USER_TABLE‘))--查询第@jishu个表的object_id declare @tab_name varchar(50); set @tab_name=(select name from sys.objects where object_id=@object_id)--根据查询出的object_id查询表名 declare @schema varchar(50)--存放表架构 set @schema=(select b.name from sys.objects a left join sys.schemas b on a.schema_id=b.schema_id where a.name=@tab_name)--根据表明查询表的架构 set @str_sql=create proc +@tab_name+_pro as begin /*set identity_insert dataName.+@tab_name+ on*/ delete dataName.+@tab_name+ where declare @pk_col varchar(50)--存放主键列名 --创建一个游标 declare cur_my cursor scroll for select d.name from sys.objects as a left join sys.indexes as b on a.object_id=b.object_id and b.is_primary_key=1 left join sys.sysindexkeys as c on b.index_id=c.indid and c.id=a.object_id left join sys.syscolumns as d on c.colid=d.colid and d.id=a.object_id where a.name=@tab_name open cur_my--打开游标 fetch first from cur_my into @pk_col --print @str_sql set @str_sql=@str_sql+@pk_col+ in(select +@pk_col+ from +@schema+.+@tab_name+) while(@@fetch_status=0) begin fetch next from cur_my into @pk_col if(@@fetch_status=0) begin set @str_sql=@str_sql+ and +@pk_col+ in(select +@pk_col+ from +@schema+.+@tab_name+) end end --print @str_sql close cur_my--关闭游标 deallocate cur_my--删除创建的游标 set @col_count=(select count(name) from sys.syscolumns where id=@object_id) declare @jishu_col int set @jishu_col=1 declare @all_name varchar(max) while(@jishu_col<=@col_count) begin declare @col_name varchar(50)--存放查询出的列名 set @col_name=(select top 1 name from sys.syscolumns where id=@object_id and name not in ( select top (@jishu_col-1) name from sys.syscolumns where id=@object_id)) if(@jishu_col=1) begin --set @str_sql=@str_sql+@col_name+‘ in(select ‘+@col_name+‘ from ‘+@tab_name+‘)‘ set @all_name=@col_name+, set @str_sql=@str_sql+ insert into dataName.+@tab_name+(cols)+ select cols from +@schema+.+@tab_name end if(@jishu_col=@col_count) begin set @all_name=@all_name+@col_name end else if(@jishu_col!=1) set @all_name=@all_name+@col_name+, set @jishu_col=@jishu_col+1 --自增 end set @str_sql=@str_sql+/* set identity_insert dataName.+@tab_name+ off*/ end set @str_sql=replace(@str_sql,cols,@all_name) set @str_sql=replace(@str_sql,dataName,shenpan_text.+@schema) print @str_sql set @jishu_tab=@jishu_tab+1--自增 break end set nocount off --end --结束

感觉还有很多缺点,望各位博友多多指教。