SQL decryption ctext field Content Function

-- SQL decryption ctext field Content Function
-- Exec sp_decrypt 'constraint name'
-- Exec sp_decrypt 'df _ insurancerecord_periodunit'
-- Exec sp_decrypt 'stored procedure name'
-- Exec sp_decrypt 'SP _ searchprinting_insurancerecord'

-- Main purpose: Read the field content in systemcomments. Including constraints and stored procedures.

-- The original text should be from idea. Saving me a lot of time. I was going to first find the crack Function in the ox character transfer format, or the content of the viabanry (8000) Field decrypted in C.

-- There are some comments that will wrap when the storage process exceeds 4000. When an error occurs, I will fix it in time.

Create procedure sp_decrypt (@ objectname varchar (50 ))
As
Begin
Set nocount on

Begin tran
Declare @ objectname1 varchar (100), @ orgvarbin varbinary (8000)
Declare @ sql1 nvarchar (4000), @ sql2 varchar (8000), @ sql3 nvarchar (4000), @ sql4 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'
Begin
Declare @ parent_obj varchar (255), @ tr_parent_xtype varchar (10)
Select @ parent_obj = parent_obj from sysobjects where id = object_id (@ objectname)
Select @ tr_parent_xtype = xtype from sysobjects where id = @ parent_obj
If @ tr_parent_xtype = 'V'
Begin
Set @ sql1 = 'alter trigger' + @ objectname + 'on' + object_name (@ parentid) + 'with encryption insterd of insert as print 1'
End
Else
Begin
Set @ sql1 = 'alter trigger' + @ objectname + 'on' + object_name (@ parentid) + 'with encryption for insert as print 1'
End

End
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 as F'

Set @ q = Len (@ sql1)
Set @ sql1 = @ sql1 + replicate ('-', 4000-@ q)
Select @ sql2 = replicate ('-', 8000)
Set @ sql3 = 'exec (@ sql1'
Select @ colid = max (colid) from # temp where number = @ k
Set @ n = 1
While @ n <= ceiling (1.0 * (@ colid-1)/2) and Len (@ sql3) <= 3996
Begin
Set @ sql3 = @ sql3 + '+ @'
Set @ n = @ n + 1
End
Set @ sql3 = @ sql3 + ')'
Exec sp_executesql @ sql3, n' @ sql1 nvarchar (4000), @ varchar (8000) ', @ sql1 = @ sql1, = @ sql2

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'
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'
Begin

If @ tr_parent_xtype = 'V'
Begin
Set @ origsptext2 = 'create trigger' + @ objectname + 'on' + object_name (@ parentid) + 'with encryption instead of insert as print 1'
End
Else
Begin
Set @ origsptext2 = 'create trigger' + @ objectname + 'on' + object_name (@ parentid) + 'with encryption for insert as print 1'
End

End

If @ type = 'V'
Set @ origsptext2 = 'create view' + @ objectname + 'with encryption as select 1 as F'

Set @ q = 4000-len (@ origsptext2)
Set @ origsptext2 = @ origsptext2 + replicate ('-', @ q)
End
Else
Begin
Set @ origsptext2 = replicate ('-', 4000)
End
Set @ I = 1

Set @ resultsp = replicate (N 'A', (datalength (@ origsptext1)/2 ))

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

Set @ n = @ n + 1

End

End
Set @ k = @ k + 1
End

Drop table # temp
Rollback tran
End