用: select a.text from syscomments a inner join sys.objects b on a.id=b.object_id where b.type in('p','tr','V') and a.text is not null 找出所有要改的内容,将 create 改成 alter,把存储过程和视图说明中第一个 as 改成 WITH ENCRYPTION as ,把触发器说明中的 after/for/instead of 前面加上 WITH ENCRYPTION ,再用游标或动态语句的方法,重新执行一下就OK了.
--自动加密示例 --下列所建加密程序段仅为说明如何加密 create table tb(id int,col1 nvarchar(10)) insert into tb select 1,'aaa' union all select 2,'bbb' create table tb2(id int,col1 nvarchar(10)) go create procedure gettb as select * from tb go create procedure gettb1 WITH ENCRYPTION as select * from tb go create trigger trigger1 on tb after insert as insert into tb2 select * from inserted go create trigger trigger2 on tb WITH ENCRYPTION after delete as delete from tb2 where id in(select id from deleted) go create view view1 as select * from tb go create view view2 WITH ENCRYPTION as select * from tb2 go select b.name,left(a.text,20)text from syscomments a inner join sys.objects b on a.id=b.object_id where b.type in('p','tr','v') /* --text值为null者是已加密的 name text ----------------------------- -------------------- gettb create procedure get gettb1 NULL trigger1 create trigger trigg trigger2 NULL view1 create view view1 as view2 NULL(6 行受影响) */ go --以下是加密过程 declare @sql nvarchar(4000),@i int set @i=1 ;with c1 as( select stuff(ltrim(replace(a.text,char(13)+char(10),' ')),1,6,'Alter')as txt from syscomments a inner join sys.objects b on a.id=b.object_id where b.type ='p' and a.text is not null ),c2 as( select stuff(ltrim(replace(a.text,char(13)+char(10),' ')),1,6,'Alter')as txt from syscomments a inner join sys.objects b on a.id=b.object_id where b.type='tr' and a.text is not null ),c3 as( select stuff(ltrim(replace(a.text,char(13)+char(10),' ')),1,6,'Alter')as txt from syscomments a inner join sys.objects b on a.id=b.object_id where b.type='V' and a.text is not null ) select ROW_NUMBER() over(order by txt) rm,txt into # from( select stuff(txt,charindex(' as ',txt),0,' WITH ENCRYPTION')txt from c1 union all select stuff(txt,charindex(' after ',txt),0,' WITH ENCRYPTION')txt from c2 where charindex(' after ',txt)>0 union all select stuff(txt,charindex(' for ',txt),0,' WITH ENCRYPTION')txt from c2 where charindex(' for ',txt)>0 union all select stuff(txt,charindex(' instead of ',txt),0,' WITH ENCRYPTION')txt from c2 where charindex(' instead of ',txt)>0 union all select stuff(txt,charindex(' as ',txt),0,' WITH ENCRYPTION')txt from c3 )c while exists(select 1 from # where rm>=@i) begin select @sql=txt from # where rm=@i exec(@sql) set @i=@i+1 end go select b.name,a.text from syscomments a inner join sys.objects b on a.id=b.object_id where b.type in('p','tr','v') go drop procedure gettb,gettb1 drop view dbo.view1,dbo.view2 drop table tb,tb2,# /* name text --------------------------- ------------------------------------------------- gettb NULL gettb1 NULL trigger1 NULL trigger2 NULL view1 NULL view2 NULL(6 行受影响) */
select a.text
from syscomments a inner join sys.objects b on a.id=b.object_id
where b.type in('p','tr','V') and a.text is not null
找出所有要改的内容,将 create 改成 alter,把存储过程和视图说明中第一个 as 改成 WITH ENCRYPTION as ,把触发器说明中的 after/for/instead of 前面加上 WITH ENCRYPTION ,再用游标或动态语句的方法,重新执行一下就OK了.
--下列所建加密程序段仅为说明如何加密
create table tb(id int,col1 nvarchar(10))
insert into tb select 1,'aaa' union all select 2,'bbb'
create table tb2(id int,col1 nvarchar(10))
go
create procedure gettb
as
select * from tb
go
create procedure gettb1 WITH ENCRYPTION as
select * from tb
go
create trigger trigger1 on tb after insert as
insert into tb2 select * from inserted
go
create trigger trigger2 on tb WITH ENCRYPTION after delete as
delete from tb2 where id in(select id from deleted)
go
create view view1 as
select * from tb
go
create view view2 WITH ENCRYPTION as
select * from tb2
go
select b.name,left(a.text,20)text from syscomments a inner join sys.objects b on a.id=b.object_id where b.type in('p','tr','v')
/*
--text值为null者是已加密的
name text
----------------------------- --------------------
gettb create procedure get
gettb1 NULL
trigger1 create trigger trigg
trigger2 NULL
view1 create view view1 as
view2 NULL(6 行受影响)
*/
go
--以下是加密过程
declare @sql nvarchar(4000),@i int
set @i=1
;with c1 as(
select stuff(ltrim(replace(a.text,char(13)+char(10),' ')),1,6,'Alter')as txt
from syscomments a inner join sys.objects b on a.id=b.object_id
where b.type ='p' and a.text is not null
),c2 as(
select stuff(ltrim(replace(a.text,char(13)+char(10),' ')),1,6,'Alter')as txt
from syscomments a inner join sys.objects b on a.id=b.object_id
where b.type='tr' and a.text is not null
),c3 as(
select stuff(ltrim(replace(a.text,char(13)+char(10),' ')),1,6,'Alter')as txt
from syscomments a inner join sys.objects b on a.id=b.object_id
where b.type='V' and a.text is not null
)
select ROW_NUMBER() over(order by txt) rm,txt into # from(
select stuff(txt,charindex(' as ',txt),0,' WITH ENCRYPTION')txt from c1
union all
select stuff(txt,charindex(' after ',txt),0,' WITH ENCRYPTION')txt from c2 where charindex(' after ',txt)>0
union all
select stuff(txt,charindex(' for ',txt),0,' WITH ENCRYPTION')txt from c2 where charindex(' for ',txt)>0
union all
select stuff(txt,charindex(' instead of ',txt),0,' WITH ENCRYPTION')txt from c2 where charindex(' instead of ',txt)>0
union all
select stuff(txt,charindex(' as ',txt),0,' WITH ENCRYPTION')txt from c3
)c
while exists(select 1 from # where rm>=@i)
begin
select @sql=txt from # where rm=@i
exec(@sql)
set @i=@i+1
end
go
select b.name,a.text from syscomments a inner join sys.objects b on a.id=b.object_id where b.type in('p','tr','v')
go
drop procedure gettb,gettb1
drop view dbo.view1,dbo.view2
drop table tb,tb2,#
/*
name text
--------------------------- -------------------------------------------------
gettb NULL
gettb1 NULL
trigger1 NULL
trigger2 NULL
view1 NULL
view2 NULL(6 行受影响)
*/