Copy codeThe Code is as follows:
Create PROCEDURE sp_decrypt (@ objectName varchar (50 ))
AS
Begin
Begin tran
Declare @ objectname1 varchar (100), @ orgvarbin varbinary (8000)
Declare @ sql1 nvarchar (4000), @ sql2 nvarchar (4000), @ sql3 nvarchar (4000), @ sql4 nvarchar (4000), @ sql5 nvarchar (4000 ), @ sql6 nvarchar (4000), @ sql7 nvarchar (4000), @ sql8 nvarchar (4000), @ sql9 nvarchar (4000), @ sql10 nvarchar (4000)
DECLARE @ OrigSpText1 nvarchar (4000), @ OrigSpText2 nvarchar (4000), @ OrigSpText3 nvarchar (4000), @ resulnvtsp archar (4000)
Declare @ I int, @ status int, @ type varchar (10), @ parentid int
Declare @ colid int, @ n int, @ q int, @ j int, @ k int, @ encrypted int, @ number int
Select @ type = xtype, @ parentid = parent_obj from sysobjects where id = object_id (@ ObjectName)
Create table # temp (number int, colid int, ctext varbinary (8000), encrypted int, status int)
Insert # temp SELECT number, colid, ctext, encrypted, status FROM syscomments WHERE id = object_id (@ objectName)
Select @ number = max (number) from # temp
Set @ k = 0
While @ k <= @ number
Begin
If exists (select 1 from syscomments where id = object_id (@ objectname) and number = @ k)
Begin
If @ type = 'P'
Set @ sql1 = (case when @ number> 1 then 'alter PROCEDURE '+ @ objectName +'; '+ rtrim (@ k) +' with encryption'
Else 'alter PROCEDURE '+ @ objectName +' with encryption'
End)
If @ type = 'tr'
Set @ sql1 = 'alter trigger' + @ objectname + 'on' + OBJECT_NAME (@ parentid) + 'with encryption for insert as print 1'
If @ type = 'fn 'or @ type = 'tf' or @ type = 'if'
Set @ sql1 = (case @ type when 'tf' then
'Alter function' + @ objectName + '(@ a char (1) returns @ B table (a varchar (10 )) with encryption as begin insert @ B select @ a return end'
When 'fn 'then
'Alter function' + @ objectName + '(@ a char (1) returns char (1) with encryption as begin return @ a end'
When 'if' then
'Alter function' + @ objectName + '(@ a char (1) returns table with encryption as return select @ a as'
End)
If @ type = 'V'
Set @ sql1 = 'alter view' + @ objectname + 'with encryption as select 1'
Set @ q = len (@ sql1)
Set @ sql1 = @ sql1 + REPLICATE ('-', 4000-@ q)
Select @ sql2 = REPLICATE ('-', 4000), @ sql3 = REPLICATE ('-', 4000), @ sql4 = REPLICATE ('-', 4000 ), @ sql5 = REPLICATE ('-', 4000), @ sql6 = REPLICATE ('-', 4000), @ sql7 = REPLICATE ('-', 4000 ), @ sql8 = REPLICATE ('-', 4000), @ sql9 = REPLICATE ('-', 4000), @ sql10 = REPLICATE ('-', 4000)
Exec (@ sql1 + @ sql2 + @ sql3 + @ sql4 + @ sql5 + @ sql6 + @ sql7 + @ sql8 + @ sql9 + @ sql10)
End
Set @ k = @ k + 1
End
Set @ k = 0
While @ k <= @ number
Begin
If exists (select 1 from syscomments where id = object_id (@ objectname) and number = @ k)
Begin
Select @ colid = max (colid) from # temp where number = @ k
Set @ n = 1
While @ n <= @ colid
Begin
Select @ OrigSpText1 = ctext, @ encrypted = encrypted, @ status = status FROM # temp WHERE colid = @ n and number = @ k
SET @ OrigSpText3 = (SELECT ctext FROM syscomments WHERE id = object_id (@ objectName) and colid = @ n and number = @ k)
If @ n = 1
Begin
If @ type = 'P'
SET @ OrigSpText2 = (case when @ number> 1 then 'create PROCEDURE '+ @ objectName +'; '+ rtrim (@ k) +' with encryption'
Else 'create PROCEDURE '+ @ objectName +' with encryption'
End)
If @ type = 'fn 'or @ type = 'tf' or @ type = 'if' -- correct the error.
SET @ OrigSpText2 = (case @ type when 'tf' then
'Create function' + @ objectName + '(@ a char (1) returns @ B table (a varchar (10 )) with encryption as begin insert @ B select @ a return end'
When 'fn 'then
'Create function' + @ objectName + '(@ a char (1) returns char (1) with encryption as begin return @ a end'
When 'if' then
'Create function' + @ objectName + '(@ a char (1) returns table with encryption as return select @ a as'
End)
If @ type = 'tr'
Set @ OrigSpText2 = 'create trigger' + @ objectname + 'on' + OBJECT_NAME (@ parentid) + 'with encryption for insert as print 1'
If @ type = 'V'
Set @ OrigSpText2 = 'create view' + @ objectname + 'with encryption as select 1'
Set @ q = 4000-len (@ OrigSpText2)
Set @ OrigSpText2 = @ OrigSpText2 + REPLICATE ('-', @ q)
End
Else
Begin
SET @ OrigSpText2 = REPLICATE ('-', 4000)
End
-- Start counter
SET @ I = 1
-- Fill temporary variable
SET @ resultsp = replicate (N 'A', (datalength (@ OrigSpText1)/2 ))
-- Loop
WHILE @ I <= datalength (@ OrigSpText1)/2
BEGIN
SET @ resultsp = stuff (@ resultsp, @ I, 1, NCHAR (UNICODE (substring (@ OrigSpText1, @ I, 1) ^
(UNICODE (substring (@ OrigSpText2, @ I, 1) ^
UNICODE (substring (@ OrigSpText3, @ I, 1 )))))
SET @ I = @ I + 1
END
Set @ orgvarbin = cast (@ OrigSpText1 as varbinary (8000 ))
Set @ resultsp = (case when @ encrypted = 1
Then @ resultsp
Else convert (nvarchar (4000), case when @ status & 2 = 2 then uncompress (@ orgvarbin) else @ orgvarbin end)
End)
Print @ resultsp
-- Execute (@ resultsp)
Set @ n = @ n + 1
End
End
Set @ k = @ k + 1
End
Drop table # temp
Rollback tran
End
GO