发错了!原文如下:
各位帮忙看看邹键大哥的代码是什么意思呢?
看了很久,懂的地方作了注释剩下的还请各位帮忙>
IF EXISTS(select * from dbo.sysobjects where id =object_id(‘[dbo].[p_copydb]’) and OBJCETPROPERTY(id , ‘IsProcedure’) = 1 )
Procedure [dbo].[p_copydb]
(1)以上是何功能?
1.创建存储过程.
CREATE PROC p_copydb
@ s_dbname sysname, --要复制数据的数据库(源数据库)
@ n_dbname sysname –接收数据的数据库(目标数据库)
AS
DECLARE @sql nvarchar(4000)
-------禁用约束/触发器,防止复制时的数据冲突
set @sql =’ declare #tbc cursor for select name from ‘+@n_dbname+..((2)这里为什么用'..'要添加什么?)sysobjects where xtype((3)这是什么字段)=’’U’’ and status>=0 ‘
Exec(@sql )
Declare @TableName(原来是tb) sysname
Open #tbc((4)什么意思?)
Fetch next from #tbc into @TableName
While @@fetch_status=0(用的是全局变量.以后好象是没用)
Begin
Set @sql=’ Alter Table’+ @n_dbname+’…[‘+@TableName+’] NOCHECK CONSTRAINT ALL ‘
Exec(@sql)
Set @sql = ‘Alter Table ’ + @n_dbname+’…[‘+@TableName+’] disable trigger ALL’
Exec(@sql)
Fetch next from #tbc into @TableName
End
Close #tbc-------复制数据
declare @sqll varchar(8000)
set @sql=’declare #tbc cursor for select a.name from’+ @s_dbname+ ‘..sysobjects a INNER JOIN ‘+@n_dbname + ‘…sysobjects b ON a.name=b.name where a.xtype = ‘’U’’ and b.xtype=’’U’’’
Exec(@sql)
Open #Table
Fetch next from #Table INTO @TableName
While @@fetch_status=0
Begin
Select @sqll=’’, @sqll=@sqll+’’ ,[‘’+a.name+’’] ‘’ from (select name from ‘+@s_dbname+’..syscoulumns where id in (select id from ‘+@s_dbname+’..sysobjects where name = ‘’’+@TableName+’’’)) b ON a.name=b.name ‘
Exec sp_executesql @sql , ‘ @sqll nvarchar(4000) out ‘ , @sqll out
Select @sqll = substring(@sqll , 2, 8000)
Exec('insert into '+@n_dbname+'..['+@TableName+@sql1+') select '+@sql1+' from '+@s_dbname+'..['+@TableName+']')
if @@error<>0(功能是什么?)
print('insert into '+@n_dbname+'..['+@TableName+']('+@sql1+')
select '+@sql1+' from '+@s_dbname+'..['+@TableName+']')
fetch next from #Table into @TableName
Endclose #Table(tb)
deallocate #(tb)Table--数据复制完成后启用约束
open #tbc
fetch next from #tbc into @TableName
while @@fetch_status=0
begin
set @sql='Alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ALL'
exec(@sql)
set @sql='Alter table '+@n_dbname+'..['+@TableName+'] Enble Trigger ALL'
exec(@sql)
fetch next from #tbc into @TableName
end
close #tbc
deallocate #tbc
GO /*--调用示例 exec p_copydb 'bns_aa','bns_new'('bns_aa,bns_new'是不是源数据库与目标数据库的名称?)
exec p_copydb 'acc_五医','acc_演示数据8'(同上)
各位帮忙看看邹键大哥的代码是什么意思呢?
看了很久,懂的地方作了注释剩下的还请各位帮忙>
IF EXISTS(select * from dbo.sysobjects where id =object_id(‘[dbo].[p_copydb]’) and OBJCETPROPERTY(id , ‘IsProcedure’) = 1 )
Procedure [dbo].[p_copydb]
(1)以上是何功能?
1.创建存储过程.
CREATE PROC p_copydb
@ s_dbname sysname, --要复制数据的数据库(源数据库)
@ n_dbname sysname –接收数据的数据库(目标数据库)
AS
DECLARE @sql nvarchar(4000)
-------禁用约束/触发器,防止复制时的数据冲突
set @sql =’ declare #tbc cursor for select name from ‘+@n_dbname+..((2)这里为什么用'..'要添加什么?)sysobjects where xtype((3)这是什么字段)=’’U’’ and status>=0 ‘
Exec(@sql )
Declare @TableName(原来是tb) sysname
Open #tbc((4)什么意思?)
Fetch next from #tbc into @TableName
While @@fetch_status=0(用的是全局变量.以后好象是没用)
Begin
Set @sql=’ Alter Table’+ @n_dbname+’…[‘+@TableName+’] NOCHECK CONSTRAINT ALL ‘
Exec(@sql)
Set @sql = ‘Alter Table ’ + @n_dbname+’…[‘+@TableName+’] disable trigger ALL’
Exec(@sql)
Fetch next from #tbc into @TableName
End
Close #tbc-------复制数据
declare @sqll varchar(8000)
set @sql=’declare #tbc cursor for select a.name from’+ @s_dbname+ ‘..sysobjects a INNER JOIN ‘+@n_dbname + ‘…sysobjects b ON a.name=b.name where a.xtype = ‘’U’’ and b.xtype=’’U’’’
Exec(@sql)
Open #Table
Fetch next from #Table INTO @TableName
While @@fetch_status=0
Begin
Select @sqll=’’, @sqll=@sqll+’’ ,[‘’+a.name+’’] ‘’ from (select name from ‘+@s_dbname+’..syscoulumns where id in (select id from ‘+@s_dbname+’..sysobjects where name = ‘’’+@TableName+’’’)) b ON a.name=b.name ‘
Exec sp_executesql @sql , ‘ @sqll nvarchar(4000) out ‘ , @sqll out
Select @sqll = substring(@sqll , 2, 8000)
Exec('insert into '+@n_dbname+'..['+@TableName+@sql1+') select '+@sql1+' from '+@s_dbname+'..['+@TableName+']')
if @@error<>0(功能是什么?)
print('insert into '+@n_dbname+'..['+@TableName+']('+@sql1+')
select '+@sql1+' from '+@s_dbname+'..['+@TableName+']')
fetch next from #Table into @TableName
Endclose #Table(tb)
deallocate #(tb)Table--数据复制完成后启用约束
open #tbc
fetch next from #tbc into @TableName
while @@fetch_status=0
begin
set @sql='Alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ALL'
exec(@sql)
set @sql='Alter table '+@n_dbname+'..['+@TableName+'] Enble Trigger ALL'
exec(@sql)
fetch next from #tbc into @TableName
end
close #tbc
deallocate #tbc
GO /*--调用示例 exec p_copydb 'bns_aa','bns_new'('bns_aa,bns_new'是不是源数据库与目标数据库的名称?)
exec p_copydb 'acc_五医','acc_演示数据8'(同上)
打开游标,就像指针一样