如何复制SQL Server 2008 的CLR扩展到别的数据库
我的一个数据有一些用.NET开发的存储过程和自定义函数
那是直接添加到数据库的,没有DLL文件,也没有源代码
请问我可以把这些存储过程和函数复制到别的数据库吗?
需要怎么操作,还有有没有办法可以反编译出这些对象的.NET源代码呢? 等待你的好消息,希望能给出一个完整的SQL代码,最好不需要增加其它函数之类的啦

我先写成这样，下面的代码能动态产生创建clr程序集的sql语句，还能产生基于clr程序集的用户定义函数的创建sql语句，你先试试：

user 你的数据库
go

--1.产生crl程序集的sql

--定义表变量，临时存储中间结果集
declare @tb table
(
name nvarchar(100),
permission_set int,
content varbinary(max),
rownum int,
create_clr_sql nvarchar(max)
);

insert into @tb
select a.name,
       a.permission_set,
       af.content,
       ROW_NUMBER() over(order by @@servername) as rownum,
       null
from sys.assembly_files af
inner join sys.assemblies a
        on af.assembly_id = a.assembly_id
where a.is_user_defined = 1


--select * from @tb

--从表变量中每次取出一条数据，通过内存循环把varbinary转化为varchar
--最后，拼接产生clr的sql语句，update到表变量中的create_clr_sql字段中 
declare @outer_i int;  --外层循环变量
declare @count int;   
 
declare @bin varbinary(max)
declare @bin_convert_varchar varchar(max)
declare @inner_i int   --内存循环变量

set @outer_i = 1;
set @count = (select COUNT(*) from @tb);

while @outer_i <= @count
begin
   set @bin = (select content from @tb where rownum = @outer_i)

   select @bin_convert_varchar = '',
          @inner_i = datalength(@bin);
       
   while @inner_i>0
   begin
     select @bin_convert_varchar=
                substring('0123456789ABCDEF',substring(@bin,@inner_i,1)/16+1,1)+
                substring('0123456789ABCDEF',substring(@bin,@inner_i,1)%16+1,1)+
                @bin_convert_varchar,
            @inner_i=@inner_i-1
   end
   
   update @tb 
   set create_clr_sql = N'create assembly ['+name+']' +
                        N' AUTHORIZATION [dbo] ' + 
                        N'FROM 0x' + @bin_convert_varchar + 
                        N' WITH PERMISSION_SET = ' + 
                        case permission_set 
                             when 1 then 'SAFE'
                             when 2 then 'EXTERNAL'
                             when 3 then 'UNSAFE'
                        end 
   where rownum = @outer_i  

   set @outer_i = @outer_i + 1   
end


--创建crl程序集的sql
select create_clr_sql from @tb 
 



--2.产生函数定义的sql

--创建基于clr程序集的用户自定义函数
select 'create function [dbo].[' + o.name + '](' +

stuff(
  (select ',' + c.name +' ' + tp.name + 
 CASE WHEN tp.name in ('numeric','decimal') 
   THEN '(' + CAST(c.precision AS VARCHAR) + 
',' + CAST(c.scale AS VARCHAR) +
')'
            
  WHEN tp.name in ('varbinary','varchar') 
   THEN case when c.max_length <> -1
  then '(' + CAST(c.max_length AS VARCHAR) + ')'
 else '(max)' 
end
            
  WHEN tp.name = 'nvarchar' 
   then case when c.max_length <> -1
  then '(' + CAST(c.max_length/2 AS VARCHAR) + ')'
 else '(max)' 
end
            
  when tp.name = 'nchar'
   then '(' + CAST(c.max_length/2 AS VARCHAR) + ')'
            
  WHEN tp.name IN ('binary','bit','char')
   THEN '(' + CAST(c.max_length AS VARCHAR) + 
')' 
  ELSE ''
 END
from sys.all_parameters c
inner join sys.types tp
on c.system_type_id = tp.system_type_id
   and c.user_type_id = tp.user_type_id

where c.object_id = o.object_id
  and c.is_output = 0
        for xml path('')
       ),
       1,1,''
     ) +
     
    (select ') returns ' + c.name +' ' + tp.name + 
 CASE WHEN tp.name in ('numeric','decimal') 
   THEN '(' + CAST(c.precision AS VARCHAR) + 
',' + CAST(c.scale AS VARCHAR) +
')'
            
  WHEN tp.name in ('varbinary','varchar') 
   THEN case when c.max_length <> -1
  then '(' + CAST(c.max_length AS VARCHAR) + ')'
 else '(max)' 
end
            
  WHEN tp.name = 'nvarchar' 
   then case when c.max_length <> -1
  then '(' + CAST(c.max_length/2 AS VARCHAR) + ')'
 else '(max)' 
end
            
  when tp.name = 'nchar'
   then '(' + CAST(c.max_length/2 AS VARCHAR) + ')'
            
  WHEN tp.name IN ('binary','bit','char')
   THEN '(' + CAST(c.max_length AS VARCHAR) + 
')' 
  ELSE ''
 END
from sys.all_parameters c
inner join sys.types tp
on c.system_type_id = tp.system_type_id
   and c.user_type_id = tp.user_type_id

where c.object_id = o.object_id
  and c.is_output = 1
       ) + 

       ' WITH EXECUTE AS CALLER AS EXTERNAL name [' + a.name + N'].'+
       '['+ am.assembly_class +'].[' + 
       am.assembly_method + '] ;' collate Chinese_PRC_CI_AS
       
from sys.assemblies a
inner join sys.assembly_modules am
        on am.assembly_id = a.assembly_id
inner join sys.objects o
        on am.object_id = o.object_id
  

--SQL SERVER 2008中2进制转16进制字符串可以用CONVERT，如果是2008之前，请用函数，具体参考http://support.microsoft.com/kb/104829

USE 源数据库
GO
DECLARE @CRL_Query  VARCHAR(MAX)

SELECT @CRL_Query = 'CREATE ASSEMBLY [' + a.name + ']' + CHAR(13)
+ 'FROM ' + CONVERT(VARCHAR(MAX),af.Content,1) + CHAR(13) 
 
+ 'WITH PERMISSION_SET = ' + CASE permission_set 
                              WHEN 1 THEN 'SAFE'
                              WHEN 2 THEN 'EXTERNAL'
                              WHEN 3 THEN 'UNSAFE' END + ';'+ CHAR(13)
FROM sys.assembly_files af
INNER JOIN sys.assemblies a ON af.assembly_id = a.assembly_id
WHERE a.is_user_defined = 1

use 目标数据库

ALTER DATABASE TEST SET TRUSTWORTHY ON;

EXEC (@CRL_Query)