找到解决方法,与各位共享.CREATE PROC sp_CopyObject @S_DatabaseName sysname, --要复制对象的源数据库名 @D_DatabaseName sysname=NULL, --复制对象的目标数据库名,不指定则为当前库名 @TypeList nvarchar(50)=NULL --以逗号分隔的对象类型列表,只能是P/V/TR/IF/TF/FN之一或者是它们的组合.不指定是所有对象 AS IF DB_ID(@S_DatabaseName) IS NULL BEGIN RAISERROR('源数据库 "%s" 不存在',12,16,@S_DatabaseName) RETURN END IF DB_ID(@D_DatabaseName) IS NULL SET @D_DatabaseName=DB_NAME()
DECLARE @sql nvarchar(4000) --处理对象类型列表 SET @sql=N'' SELECT @sql=@sql+N','+QUOTENAME(xtype,N'''') FROM( SELECT xtype=N'P' UNION ALL SELECT N'IF' UNION ALL SELECT xtype=N'V' UNION ALL SELECT N'TF' UNION ALL SELECT xtype=N'TR' UNION ALL SELECT N'FN' )a WHERE CHARINDEX(N','+xtype+N',',N','+@TypeList+N',')> 0 IF @@ROWCOUNT=0 OR @sql='' SET @TypeList='N''P'',N''V'',N''TR'',N''IF'',''TF'',''FN''' ELSE SET @TypeList=STUFF(@sql,1,1,N'')
--获取要复制对象(存储,视图,触发器)的数据 CREATE TABLE #(name sysname,xtype char(2),number smallint,colid smallint,text nvarchar(4000)) SET @sql=N' INSERT #(name,xtype,number,colid,text) SELECT o.name,o.xtype,m.number,m.colid,m.text FROM '+QUOTENAME(@S_DatabaseName)+N'.dbo.sysobjects o,' +QUOTENAME(@S_DatabaseName)+N'.dbo.syscomments m WHERE o.id=m.id AND m.encrypted=0 AND o.xtype IN('+@TypeList+N') AND o.status> =0' EXEC sp_executesql @sql
DECLARE tb CURSOR LOCAL FOR SELECT N'USE '+QUOTENAME(@D_DatabaseName)+N' DECLARE @name sysname,@number smallint SELECT @name='+QUOTENAME(name,N'''')+N', @number='+CAST(number as varchar)+N' DECLARE @1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(1000) SELECT @1=N'''',@2=N'''',@3=N'''' SELECT @1=@1+N'',@''+RTRIM(colid)+N'' nvarchar(4000)'', @2=@2+N'',@''+RTRIM(colid) +N''=CASE colid WHEN ''+RTRIM(colid) +N'' THEN text ELSE @''+RTRIM(colid)+N'' END'', @3=@3+N''+@''+RTRIM(colid) FROM # WHERE name=@name AND number=@number ORDER BY colid SELECT @1=STUFF(@1,1,1,N''''), @2=STUFF(@2,1,1,N''''), @3=STUFF(@3,1,1,N''''), @4=N'' WHERE name=''+QUOTENAME(@name,N'''''''') +N'' AND number=''+RTRIM(@number) IF @NUMBER <2 AND EXISTS(SELECT * FROM sysobjects WHERE name=@name AND xtype=' +QUOTENAME(xtype,N'''')+N') DROP '+CASE xtype WHEN N'P' THEN N'PROC' WHEN N'V' THEN N'VIEW' WHEN N'TR' THEN N'TRIGGER' ELSE N'FUNCTION' END +N' '+QUOTENAME(name)+N' EXEC(N''DECLARE ''+@1+'' SELECT ''+@2+'' FROM # ''+@4+N'' EXEC(''+@3+'')'')' FROM # GROUP BY name,xtype,number OPEN tb FETCH tb INTO @sql WHILE @@FETCH_STATUS=0 BEGIN EXEC sp_executesql @sql FETCH tb INTO @sql END CLOSE tb DEALLOCATE tb
可用Osql生成腳本文件
可用企業管理器生成腳本文件通過都會采用第2種方法
这样做如果视图1依赖视图2,自动生成脚本时,视图2可能先于视图1创建,那就有问题了
这样做如果视图1依赖视图2,自动生成脚本时,视图2可能先于视图1创建,那就有问题了
@S_DatabaseName sysname, --要复制对象的源数据库名
@D_DatabaseName sysname=NULL, --复制对象的目标数据库名,不指定则为当前库名
@TypeList nvarchar(50)=NULL --以逗号分隔的对象类型列表,只能是P/V/TR/IF/TF/FN之一或者是它们的组合.不指定是所有对象
AS
IF DB_ID(@S_DatabaseName) IS NULL
BEGIN
RAISERROR('源数据库 "%s" 不存在',12,16,@S_DatabaseName)
RETURN
END
IF DB_ID(@D_DatabaseName) IS NULL SET @D_DatabaseName=DB_NAME()
DECLARE @sql nvarchar(4000)
--处理对象类型列表
SET @sql=N''
SELECT @sql=@sql+N','+QUOTENAME(xtype,N'''')
FROM(
SELECT xtype=N'P' UNION ALL SELECT N'IF' UNION ALL
SELECT xtype=N'V' UNION ALL SELECT N'TF' UNION ALL
SELECT xtype=N'TR' UNION ALL SELECT N'FN'
)a WHERE CHARINDEX(N','+xtype+N',',N','+@TypeList+N',')> 0
IF @@ROWCOUNT=0 OR @sql=''
SET @TypeList='N''P'',N''V'',N''TR'',N''IF'',''TF'',''FN'''
ELSE
SET @TypeList=STUFF(@sql,1,1,N'')
--获取要复制对象(存储,视图,触发器)的数据
CREATE TABLE #(name sysname,xtype char(2),number smallint,colid smallint,text nvarchar(4000))
SET @sql=N'
INSERT #(name,xtype,number,colid,text)
SELECT o.name,o.xtype,m.number,m.colid,m.text
FROM '+QUOTENAME(@S_DatabaseName)+N'.dbo.sysobjects o,'
+QUOTENAME(@S_DatabaseName)+N'.dbo.syscomments m
WHERE o.id=m.id
AND m.encrypted=0
AND o.xtype IN('+@TypeList+N')
AND o.status> =0'
EXEC sp_executesql @sql
DECLARE tb CURSOR LOCAL
FOR
SELECT N'USE '+QUOTENAME(@D_DatabaseName)+N'
DECLARE @name sysname,@number smallint
SELECT @name='+QUOTENAME(name,N'''')+N',
@number='+CAST(number as varchar)+N'
DECLARE @1 nvarchar(4000),@2 nvarchar(4000),@3 nvarchar(4000),@4 nvarchar(1000)
SELECT @1=N'''',@2=N'''',@3=N''''
SELECT @1=@1+N'',@''+RTRIM(colid)+N'' nvarchar(4000)'',
@2=@2+N'',@''+RTRIM(colid)
+N''=CASE colid WHEN ''+RTRIM(colid)
+N'' THEN text ELSE @''+RTRIM(colid)+N'' END'',
@3=@3+N''+@''+RTRIM(colid)
FROM #
WHERE name=@name AND number=@number
ORDER BY colid
SELECT @1=STUFF(@1,1,1,N''''),
@2=STUFF(@2,1,1,N''''),
@3=STUFF(@3,1,1,N''''),
@4=N'' WHERE name=''+QUOTENAME(@name,N'''''''')
+N'' AND number=''+RTRIM(@number)
IF @NUMBER <2 AND EXISTS(SELECT * FROM sysobjects WHERE name=@name AND xtype='
+QUOTENAME(xtype,N'''')+N') DROP '+CASE xtype
WHEN N'P' THEN N'PROC'
WHEN N'V' THEN N'VIEW'
WHEN N'TR' THEN N'TRIGGER'
ELSE N'FUNCTION' END
+N' '+QUOTENAME(name)+N'
EXEC(N''DECLARE ''+@1+''
SELECT ''+@2+'' FROM # ''+@4+N''
EXEC(''+@3+'')'')'
FROM #
GROUP BY name,xtype,number
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
EXEC sp_executesql @sql
FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb