日期:2014-05-17 浏览次数:20553 次
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_