在两个SQL Server数据库之间复制对象

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