我也写过,比如生成表的: /*取得一个数据库里所有用户表的建表代码 by gthlu 2007-5-15 */ declare @sql varchar(4000) declare @tabname varchar(100) declare @colname varchar(80),@cdefault int,@domain int,@xusertype smallint, @prec smallint,@scale int,@isnullable intcreate table #tabtext(txt varchar(4000))declare tabname_cursor cursor for select name from sysobjects where type = 'u' open tabname_cursor fetch next from tabname_cursor into @tabnamewhile @@fetch_status >= 0 begin select @sql = "if object_id('" + @tabname + "') is null " + 'create table ' + @tabname + '(' declare column_cursor cursor for select name,cdefault,domain,xusertype,prec,scale,isnullable from syscolumns where id = object_id(@tabname) order by colid open column_cursor fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@scale,@isnullable while @@fetch_status >= 0 begin select @sql = @sql + @colname + (select ' ' + name from systypes where xusertype = @xusertype) + case when @prec is null or @prec = 0 or @prec = (select prec from systypes where xusertype = @xusertype) then ' ' else '(' + cast(@prec as varchar(10)) + case when @scale is null or @scale = (select scale from systypes where xusertype = @xusertype) then ')' else ',' + cast(@scale as varchar(10)) + ')' end end + case when @cdefault is null or @cdefault = 0 then '' else (select case when charindex('create',text) = 0 then ' default ' + text else '' end from syscomments where id = @cdefault) end + case @isnullable when 0 then ' not null,' when 1 then ' null,' else ',' end
fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@scale,@isnullable end close column_cursor deallocate column_cursor fetch next from tabname_cursor into @tabname select @sql = left(@sql,len(rtrim(@sql))-1) + ')' insert into #tabtext select @sql end close tabname_cursor deallocate tabname_cursorselect txt from #tabtext drop table #tabtext--这个应该没什么问题,主要是生成过程的,还是存在有sp_helptext的毛病
可得到所有的过程、触发器等,但有个问题,单个代码超长的话,得到的会有问题:drop proc p_get_objtext go/*取数据库里的全部存储过程、触发器和视图,只适用于未加密的过程、触发器和视图 by gthlu 2007-5-15 */create procedure p_get_objtext asset nocount ondeclare @BlankSpaceAdded int ,@BasePos int ,@CurrentPos int ,@TextLength int ,@LineId int ,@AddOnLen int ,@LFCR int ,@DefinedLength int ,@SyscomText nvarchar(4000) ,@Line nvarchar(255) ,@objid int ,@objname nvarchar(776)Select @DefinedLength = 255 SELECT @BlankSpaceAdded = 0 create table #objtext(txt nvarchar(255),id int,LineId int)declare cursor_obj cursor for select object_name(id) from sysobjects where type in ('tr','p','v') open cursor_objfetch next from cursor_obj into @objname select @objid = 1 while @@fetch_status >= 0 begin CREATE TABLE #CommentText (LineId int, Text nvarchar(255) collate database_default) insert into #commenttext select 0,"if object_id('" + @objname + "') is not null " + case (select type from sysobjects where id = object_id(@objname)) when 'tr' then 'drop trigger ' + @objname when 'p' then 'drop proc ' + @objname when 'v' then 'drop view ' + @objname else '' end insert into #commenttext select 1,'go' union all select 2,char(13) DECLARE ms_crs_syscom CURSOR LOCAL FOR SELECT text FROM syscomments WHERE id = object_id(@objname) order by number,colid FOR READ ONLY SELECT @LFCR = 2 SELECT @LineId = 3 OPEN ms_crs_syscom FETCH NEXT FROM ms_crs_syscom into @SyscomText WHILE @@fetch_status >= 0 BEGIN SELECT @BasePos = 1 SELECT @CurrentPos = 1 SELECT @TextLength = LEN(@SyscomText) WHILE @CurrentPos != 0 BEGIN SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos) IF @CurrentPos != 0 BEGIN While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength BEGIN SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded) INSERT #CommentText VALUES ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 END SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'') SELECT @BasePos = @CurrentPos+2 INSERT #CommentText VALUES( @LineId, @Line ) SELECT @LineId = @LineId + 1 SELECT @Line = NULL END ELSE BEGIN IF @BasePos <= @TextLength BEGIN While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength BEGIN SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded) INSERT #CommentText VALUES ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 END SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'') if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0 BEGIN SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1 END END END END FETCH NEXT FROM ms_crs_syscom into @SyscomText END IF @Line is NOT NULL INSERT #CommentText VALUES( @LineId, @Line ) CLOSE ms_crs_syscom DEALLOCATE ms_crs_syscom insert into #commenttext select @lineid+1,char(13) union all select @lineid + 2,'go' union all select @lineid + 3,char(13) insert into #objtext select Text,@objid,lineid from #CommentText order by LineId select @objid = @objid + 1 DROP TABLE #CommentText fetch next from cursor_obj into @objname end close cursor_obj deallocate cursor_objselect * from #objtext order by id,lineid drop table #objtextreturn (0)
生成表的方法,得到表的结构信息,再生成建立脚本. 简单的获取表信息自定义函数: /* 功能:返回某一表的所有字段、存储过程、函数的参数信息 设计:OK_008 时间:2006-05 */ CREATE FUNCTION FN_GetObjColInfo (@ObjName varchar(50)) RETURNS @Return_Table TABLE( TName nvarchar(50), TypeName nvarchar(50), TypeLength nvarchar(50), Colstat Bit ) AS BEGIN INSERT @Return_Table select b.name as 字段名,c.name as 字段类型,b.length/2 as 字段长度,b.colstat as 是否自动增长 from sysobjects a inner join syscolumns b on a.id=b.id inner join systypes c on c.xusertype=b.xtype where a.name =@ObjName order by B.ColID RETURN END--是于建立存储过程和视图,使用sp_helptext就可以。
以前邹大哥写过一段语句,可以将一数据库复制到另一数据库,但我运行时,可能会出点问题,因为视图里面再调用其它视图的情况,这样在复制时,可能会提示某视图对象无效的错误 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[P_CopyDB] GO/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移 存储过程实现源数据库到目标数据库的对象和数据的复制 要求源数据库和目标数据库在同一服务器 如果是要实现不同服务器之间的复制,则需要增加验证信息 --邹建 2005.07(引用请保留此信息)--*//*--调用示例 CREATE DATABASE test EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test' DROP DATABASE test --*/ CREATE PROCEDURE P_CopyDB @Des_DB sysname, --目标数据库 @Obj_Type nvarchar(4000)=N'',--复制的对象类型,可以是下列字符串列表: -- O 所有对象,D 默认值,R 规则,P 存储过程 -- T 表,TR 触发器,DT 用户定义数据类型 -- V 视图,DATA 数据,DEL 删除目标对象 @Source_DB sysname=N'', --源数据库 @ServerName sysname=N'', --服务器名 @UserName sysname=N'', --用户名,不指定则表示使用 Windows 身份登录 @pwd sysname=N'' --密码 AS SET NOCOUNT ON DECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int, @err int,@src varchar(255), @desc varchar(255)IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAME IF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME() --创建sqldmo对象· EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT IF @err<>0 GOTO lb_Err--连接服务器 IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录 BEGIN EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1 IF @err<>0 GOTO lb_Err EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername END ELSE EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwdIF @err<>0 GOTO lb_Err--获取数据库集 EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT IF @err<>0 GOTO lb_Err--选择源数据库 EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB IF @err<>0 GOTO lb_Err--选择目标数据库 EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB IF @err<>0 GOTO lb_Err--设置复制的对象 EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT IF @err<>0 GOTO lb_Err--设置目标服务器信息 EXEC @err=sp_oasetproperty @TransferID,'DestServer',@ServerName IF @err<>0 GOTO lb_Err --设置连接用户 IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录 BEGIN EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1 IF @err<>0 GOTO lb_Err END ELSE BEGIN EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName IF @err<>0 GOTO lb_Err EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd IF @err<>0 GOTO lb_Err END --设置复制对象信息 EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB IF @err<>0 GOTO lb_Err DECLARE tb CURSOR FAST_FORWARD LOCAL FOR SELECT Name FROM( SELECT KeyWord=N',D,', Name=N'CopyAllDefaults' UNION ALL SELECT KeyWord=N',O,', Name=N'CopyAllObjects' UNION ALL SELECT KeyWord=N',R,', Name=N'CopyAllRules' UNION ALL SELECT KeyWord=N',P,', Name=N'CopyAllStoredProcedures' UNION ALL SELECT KeyWord=N',T,', Name=N'CopyAllTables' UNION ALL SELECT KeyWord=N',TR,', Name=N'CopyAllTriggers' UNION ALL SELECT KeyWord=N',DT,', Name=N'CopyAllUserDefinedDatatypes' UNION ALL SELECT KeyWord=N',V,', Name=N'CopyAllViews' UNION ALL SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst' )A WHERE CHARINDEX(KeyWord, CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0 OPEN tb FETCH tb INTO @src WHILE @@FETCH_STATUS=0 BEGIN EXEC @err=sp_oasetproperty @TransferID,@src,1 IF @err<>0 GOTO lb_Err FETCH tb INTO @src END CLOSE tb DEALLOCATE tb--复制对象 EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID IF @err<>0 GOTO lb_Err--结束 SET @err=0 GOTO lb_Exit--错误处理 lb_Err: EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc) RETURN -1lb_Exit: EXEC sp_OADestroy @Dbid EXEC sp_OADestroy @srvid EXEC sp_OADestroy @TransferID RETURN @err GO
显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本USE pubs
EXEC sp_helptext 'employee_insupd'不知道对你有用不
是根据你自己的选择要生成的对象自动生成脚本你想自己去写这个生成的脚本的方法完全可以
至于怎样写就很麻烦了,需要你对系统表都很熟悉了
还要有时间慢慢写
/*取得一个数据库里所有用户表的建表代码 by gthlu 2007-5-15 */
declare @sql varchar(4000)
declare @tabname varchar(100)
declare @colname varchar(80),@cdefault int,@domain int,@xusertype smallint,
@prec smallint,@scale int,@isnullable intcreate table #tabtext(txt varchar(4000))declare tabname_cursor cursor for select name from sysobjects where type = 'u'
open tabname_cursor
fetch next from tabname_cursor into @tabnamewhile @@fetch_status >= 0
begin
select @sql = "if object_id('" + @tabname + "') is null " + 'create table ' + @tabname + '(' declare column_cursor cursor for
select name,cdefault,domain,xusertype,prec,scale,isnullable
from syscolumns
where id = object_id(@tabname)
order by colid open column_cursor
fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@scale,@isnullable while @@fetch_status >= 0
begin
select @sql = @sql + @colname + (select ' ' + name from systypes where xusertype = @xusertype)
+ case when @prec is null or @prec = 0 or @prec = (select prec from systypes where xusertype = @xusertype) then ' '
else '(' + cast(@prec as varchar(10))
+ case when @scale is null or @scale = (select scale from systypes where xusertype = @xusertype) then ')'
else ',' + cast(@scale as varchar(10)) + ')' end end
+ case when @cdefault is null or @cdefault = 0 then ''
else (select case when charindex('create',text) = 0 then ' default ' + text else '' end from syscomments where id = @cdefault) end
+ case @isnullable when 0 then ' not null,'
when 1 then ' null,'
else ',' end
fetch next from column_cursor into @colname,@cdefault,@domain,@xusertype,@prec,@scale,@isnullable
end
close column_cursor
deallocate column_cursor fetch next from tabname_cursor into @tabname select @sql = left(@sql,len(rtrim(@sql))-1) + ')'
insert into #tabtext select @sql
end close tabname_cursor
deallocate tabname_cursorselect txt from #tabtext
drop table #tabtext--这个应该没什么问题,主要是生成过程的,还是存在有sp_helptext的毛病
go/*取数据库里的全部存储过程、触发器和视图,只适用于未加密的过程、触发器和视图 by gthlu 2007-5-15 */create procedure p_get_objtext
asset nocount ondeclare @BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int
,@DefinedLength int
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)
,@objid int
,@objname nvarchar(776)Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
create table #objtext(txt nvarchar(255),id int,LineId int)declare cursor_obj cursor for select object_name(id) from sysobjects where type in ('tr','p','v')
open cursor_objfetch next from cursor_obj into @objname
select @objid = 1
while @@fetch_status >= 0
begin CREATE TABLE #CommentText
(LineId int,
Text nvarchar(255) collate database_default) insert into #commenttext select 0,"if object_id('" + @objname + "') is not null "
+ case (select type from sysobjects where id = object_id(@objname))
when 'tr' then 'drop trigger ' + @objname
when 'p' then 'drop proc ' + @objname
when 'v' then 'drop view ' + @objname
else ''
end
insert into #commenttext select 1,'go' union all select 2,char(13) DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = object_id(@objname) order by number,colid FOR READ ONLY SELECT @LFCR = 2
SELECT @LineId = 3 OPEN ms_crs_syscom FETCH NEXT FROM ms_crs_syscom into @SyscomText WHILE @@fetch_status >= 0
BEGIN SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText) WHILE @CurrentPos != 0
BEGIN
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos) IF @CurrentPos != 0
BEGIN
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
BEGIN
IF @BasePos <= @TextLength
BEGIN
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
END FETCH NEXT FROM ms_crs_syscom into @SyscomText
END IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line ) CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom insert into #commenttext select @lineid+1,char(13) union all select @lineid + 2,'go' union all select @lineid + 3,char(13)
insert into #objtext select Text,@objid,lineid from #CommentText order by LineId
select @objid = @objid + 1
DROP TABLE #CommentText fetch next from cursor_obj into @objname end
close cursor_obj
deallocate cursor_objselect * from #objtext order by id,lineid
drop table #objtextreturn (0)
简单的获取表信息自定义函数:
/*
功能:返回某一表的所有字段、存储过程、函数的参数信息
设计:OK_008
时间:2006-05
*/
CREATE FUNCTION FN_GetObjColInfo
(@ObjName varchar(50))
RETURNS @Return_Table TABLE(
TName nvarchar(50),
TypeName nvarchar(50),
TypeLength nvarchar(50),
Colstat Bit
)
AS
BEGIN
INSERT @Return_Table
select b.name as 字段名,c.name as 字段类型,b.length/2 as 字段长度,b.colstat as 是否自动增长
from sysobjects a
inner join syscolumns b on a.id=b.id
inner join systypes c on c.xusertype=b.xtype
where a.name =@ObjName
order by B.ColID
RETURN
END--是于建立存储过程和视图,使用sp_helptext就可以。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[P_CopyDB]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[P_CopyDB]
GO/*-- 在 SQLServer 中使用SQLDMO.Transfer 实现数据迁移
存储过程实现源数据库到目标数据库的对象和数据的复制
要求源数据库和目标数据库在同一服务器
如果是要实现不同服务器之间的复制,则需要增加验证信息
--邹建 2005.07(引用请保留此信息)--*//*--调用示例 CREATE DATABASE test
EXEC P_CopyDB @Source_DB='northwind',@Des_DB='test'
DROP DATABASE test
--*/
CREATE PROCEDURE P_CopyDB
@Des_DB sysname, --目标数据库
@Obj_Type nvarchar(4000)=N'',--复制的对象类型,可以是下列字符串列表:
-- O 所有对象,D 默认值,R 规则,P 存储过程
-- T 表,TR 触发器,DT 用户定义数据类型
-- V 视图,DATA 数据,DEL 删除目标对象
@Source_DB sysname=N'', --源数据库
@ServerName sysname=N'', --服务器名
@UserName sysname=N'', --用户名,不指定则表示使用 Windows 身份登录
@pwd sysname=N'' --密码
AS
SET NOCOUNT ON
DECLARE @srvid int,@Dbid int,@S_dbid int,@D_dbid int,@TransferID int,
@err int,@src varchar(255), @desc varchar(255)IF ISNULL(@ServerName,N'')=N'' SET @ServerName=@@SERVERNAME
IF ISNULL(@Source_DB,N'')=N'' SET @Source_DB=DB_NAME() --创建sqldmo对象·
EXEC @err=sp_oacreate 'sqldmo.sqlserver',@srvid OUT
IF @err<>0 GOTO lb_Err--连接服务器
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
EXEC @err=sp_oasetproperty @srvid,'loginsecure',-1
IF @err<>0 GOTO lb_Err EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername
END
ELSE
EXEC @err=sp_oamethod @srvid,'connect',NULL,@servername,@UserName,@pwdIF @err<>0 GOTO lb_Err--获取数据库集
EXEC @err=sp_oagetproperty @srvid,'databases',@Dbid OUT
IF @err<>0 GOTO lb_Err--选择源数据库
EXEC @err=sp_oamethod @Dbid,'item',@S_dbid OUT,@Source_DB
IF @err<>0 GOTO lb_Err--选择目标数据库
EXEC @err=sp_oamethod @Dbid,'item',@D_dbid OUT,@Des_DB
IF @err<>0 GOTO lb_Err--设置复制的对象
EXEC @err=sp_oacreate 'SQLDMO.Transfer',@TransferID OUT
IF @err<>0 GOTO lb_Err--设置目标服务器信息
EXEC @err=sp_oasetproperty @TransferID,'DestServer',@ServerName
IF @err<>0 GOTO lb_Err --设置连接用户
IF ISNULL(@UserName,N'')=N'' --使用 Windows 身份登录
BEGIN
EXEC @err=sp_oasetproperty @TransferID,'DestUseTrustedConnection',1
IF @err<>0 GOTO lb_Err
END
ELSE
BEGIN
EXEC @err=sp_oasetproperty @TransferID,'DestLogin',@UserName
IF @err<>0 GOTO lb_Err EXEC @err=sp_oasetproperty @TransferID,'DestPassword',@pwd
IF @err<>0 GOTO lb_Err
END --设置复制对象信息
EXEC @err=sp_oasetproperty @TransferID,'DestDatabase',@Des_DB
IF @err<>0 GOTO lb_Err DECLARE tb CURSOR FAST_FORWARD LOCAL
FOR
SELECT Name FROM(
SELECT KeyWord=N',D,', Name=N'CopyAllDefaults' UNION ALL
SELECT KeyWord=N',O,', Name=N'CopyAllObjects' UNION ALL
SELECT KeyWord=N',R,', Name=N'CopyAllRules' UNION ALL
SELECT KeyWord=N',P,', Name=N'CopyAllStoredProcedures' UNION ALL
SELECT KeyWord=N',T,', Name=N'CopyAllTables' UNION ALL
SELECT KeyWord=N',TR,', Name=N'CopyAllTriggers' UNION ALL
SELECT KeyWord=N',DT,', Name=N'CopyAllUserDefinedDatatypes' UNION ALL
SELECT KeyWord=N',V,', Name=N'CopyAllViews' UNION ALL
SELECT KeyWord=N',DATA,',Name=N'CopyData' UNION ALL
SELECT KeyWord=N',DEL,', Name=N'DropDestObjectsFirst'
)A WHERE CHARINDEX(KeyWord,
CASE WHEN ISNULL(@Obj_Type,N'')='' THEN ',O,DATA,' ELSE @Obj_Type END)>0
OPEN tb
FETCH tb INTO @src
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @err=sp_oasetproperty @TransferID,@src,1
IF @err<>0 GOTO lb_Err
FETCH tb INTO @src
END
CLOSE tb
DEALLOCATE tb--复制对象
EXEC @err=sp_oamethod @S_dbid,'Transfer',null,@TransferID
IF @err<>0 GOTO lb_Err--结束
SET @err=0
GOTO lb_Exit--错误处理
lb_Err:
EXEC sp_oageterrorinfo NULL, @src OUT, @desc OUT
RAISERROR(N'错误编号 %#x, 错误源 "%s", 错误描述 "%s"',16,1,@err,@src,@desc)
RETURN -1lb_Exit:
EXEC sp_OADestroy @Dbid
EXEC sp_OADestroy @srvid
EXEC sp_OADestroy @TransferID
RETURN @err
GO