A. Here is the code for SQL 6.5

/************************************************/
/* REVISED REC'D 5/21/98 */
/* sp_decrypt_object Tom Sager 01/26/98 */
/* */
/* Decrypts objects (views, procedures & trigs) */
/* created with the WITH ENCRYPTION option. */
/* */
/* Uses the encrypt() built-in function to find */
/* a plaintext string that encrypts to the same */
/* value as stored in the text column of the */
/* syscomments table. */
/* */
/************************************************/
create proc sp_decrypt_object
(@objname varchar(30))
WITH ENCRYPTION
as

SET NOCOUNT ON

declare @errmsg varchar(80)
declare @encrtext varchar(255)
declare @decrtext varchar(255)
declare @testtext varchar(255)
declare @printline varchar(255)
declare @textlen int
declare @lup int
declare @match char(1)
declare @testchar smallint
declare @begblk smallint
declare @endblk smallint

if (select count(*)
from sysobjects
where name = @objname) = 0
begin
select @errmsg = 'Object '
+@objname
+' not found in database '
+DB_NAME()
print @errmsg
return 1
end

if (select count(*) from sysobjects t1,
syscomments t2
where t1.name = @objname
and t1.id = t2.id
and t2.texttype & 4 != 0) = 0
begin
select @errmsg = 'Object '
+@objname
+' is not encrypted in database '
+DB_NAME()
print @errmsg
return 1
end

DECLARE comments_cursor CURSOR for
select t1.text
from syscomments t1,
sysobjects t2
where t1.id = t2.id
and t2.name = @objname
order by t1.colid

OPEN comments_cursor

FETCH NEXT FROM comments_cursor
INTO @encrtext

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
select @decrtext = REPLICATE(' ', 255)
select @textlen = DATALENGTH(@encrtext)
select @lup = 1
select @match = 'n'
while (@lup <= @textlen)
begin
select @testchar = 0
select @match = 'n'
while (@match = 'n')
begin
select @decrtext =
STUFF(@decrtext,@lup,1,CHAR(@testchar))
select @testtext = encrypt(@decrtext)
if ASCII(SUBSTRING(@testtext,@lup,1)) =
ASCII(SUBSTRING(@encrtext,@lup,1))
begin
select @match = 'y'
end
select @testchar = @testchar + 1
if (@testchar > 255)
begin
print 'Error...no match found'
return 1
end
end
select @lup = @lup + 1
end
select @begblk = 1
select @endblk = 1
while (@endblk <= @textlen)
begin
if (substring(@decrtext,@endblk,1) = 0x0a)
begin
select @printline = @printline +
SUBSTRING(@decrtext
,@begblk
,@endblk-@begblk+1)
print @printline
select @begblk = @endblk + 1
select @endblk = @begblk
select @printline = NULL
end
select @endblk = @endblk + 1
end
select @printline = @printline +
SUBSTRING(@decrtext
,@begblk
,@endblk-@begblk+1)
END
FETCH NEXT FROM comments_cursor INTO @encrtext
END
print @printline
CLOSE comments_cursor

DEALLOCATE comments_cursor
GO