sql存储过程的解决方法

<p>create table [dbo].[sql_decode](<br />    [id] [int] identity(1,1) not null,<br />    [sqltext] [nvarchar](max) not null,<br /> constraint [id] primary key clustered <br />(<br />    [id] asc<br />)<br />) on [primary]<br /><br />go</p>

<p>CREATE PROCEDURE [dbo].[DECODE_DATABASE]<br />AS<br />SET NOCOUNT ON<br />BEGIN<br /><br />    DECLARE @PROC_NAME VARCHAR(256)<br />    SET @PROC_NAME = ''<br />    DECLARE @ROWS INT<br />    DECLARE @TEMP TABLE(<br />        NAME VARCHAR(256)<br />    )<br />    INSERT INTO @TEMP SELECT NAME FROM sysobjects WHERE TYPE = 'P' <br />    AND NAME NOT IN (<br />        'DECODE_DATABASE', 'DECODE_PROC'<br />    )<br />    SET @ROWS = @@ROWCOUNT<br />    WHILE @ROWS > 0<br />    BEGIN<br />        SELECT @PROC_NAME = NAME FROM (<br />            SELECT ROW_NUMBER() OVER (ORDER by NAME) AS ROW, NAME FROM @TEMP <br />        ) T <br />        WHERE ROW = @ROWS<br />        EXEC [DECODE_PROC] @PROC_NAME<br />        PRINT @PROC_NAME<br />        SET @ROWS = @ROWS - 1<br />    END<br />    <br />    RETURN<br />    <br />    EXEC master..xp_cmdshell 'bcp "SELECT [SQLTEXT] FROM TEST.dbo.[SQL_DECODE]" queryout C:decode.txt -c -T -S PC2011043012JUJ'<br />END<br /><br /><br />GO</p>

<p>CREATE PROCEDURE [dbo].[DECODE_PROC](<br />    @PROC_NAME SYSNAME = NULL<br />)<br />AS<br />SET NOCOUNT ON<br /><br />DECLARE @PROC_NAME_LEN INT    --存储过程名长度<br />DECLARE @MAX_COL_ID SMALLINT    --最大列ID<br />SELECT @MAX_COL_ID = MAX(subobjid) FROM sys.sysobjvalues WHERE objid = OBJECT_ID(@PROC_NAME) GROUP BY imageval<br /><br />SELECT @PROC_NAME_LEN = DATALENGTH(@PROC_NAME) + 29<br />DECLARE @REAL_01 NVARCHAR(MAX)    --真实加密存储过程数据<br />DECLARE @FACK_01 NVARCHAR(MAX)    --修改为假的存储过程，长度（40003 - 存在过程名长度），原理不明？<br />DECLARE @FACK_ENCRYPT_01 NVARCHAR(MAX)    --伪加密存储过街程数据<br />DECLARE @REAL_DECRYPT_01 NVARCHAR(MAX)    --最终解密后的数据，初始化为原始加密长度的一半的&ldquo;A&rdquo;，原理不明？<br /><br />SET @REAL_01 = (<br />    SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@PROC_NAME) AND valclass = 1 AND subobjid = 1<br />)<br /><br />DECLARE @REAL_DATA_LEN BIGINT<br />SET @REAL_DATA_LEN = DATALENGTH(@REAL_01)<br />--PRINT @REAL_DATA_LEN<br /><br />DECLARE @FACK_LEN BIGINT<br />SET @FACK_LEN = @REAL_DATA_LEN * 10    --改造：假的长度在原真实数据长度上放大10倍<br /><br />--此处需将NVARCHAR显示转换成NVARCHAR(MAX)，不然将只能产生4K长度<br />SET @FACK_01 = 'ALTER PROCEDURE ' + @PROC_NAME + ' WITH ENCRYPTION AS ' + REPLICATE(CONVERT(NVARCHAR(MAX), '-'), @FACK_LEN - @PROC_NAME_LEN)<br />--PRINT '@FACK_01 = ' + STR(LEN(@FACK_01))<br />EXECUTE (@FACK_01)<br />SET @FACK_ENCRYPT_01 = (<br />    SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@PROC_NAME) AND valclass = 1 AND subobjid = 1<br />)<br /><br />SET @FACK_01 = 'CREATE PROCEDURE ' + @PROC_NAME + ' WITH ENCRYPTION AS ' + REPLICATE(CONVERT(VARCHAR(MAX), '-'), @FACK_LEN - @PROC_NAME_LEN)<br />SET @REAL_DECRYPT_01 = REPLICATE(CONVERT(NVARCHAR(MAX), N'A'), (DATALENGTH(@REAL_01) /2))<br />--PRINT 'LEN(@REAL_DECRYPT_01) = ' + STR(LEN(@REAL_DECRYPT_01))<br /><br /><br />--按位对 @REAL_01、 @FACK_01、 @REAL_DECRYPT_01 进行异或操作。<br />DECLARE @INT_PROC_SPACE BIGINT<br />SET @INT_PROC_SPACE = 1<br />WHILE @INT_PROC_SPACE <= (DATALENGTH(@REAL_01) /2 )<br />BEGIN<br />    SET @REAL_DECRYPT_01 = STUFF(<br />        @REAL_DECRYPT_01, <br />        @INT_PROC_SPACE, <br />        1, <br />        NCHAR(UNICODE(SUBSTRING(@REAL_01, @INT_PROC_SPACE, 1)) ^ (UNICODE(SUBSTRING(@FACK_01, @INT_PROC_SPACE, 1)) ^ UNICODE(SUBSTRING(@FACK_ENCRYPT_01, @INT_PROC_SPACE, 1))))<br />    )<br />    SET @INT_PROC_SPACE = @INT_PROC_SPACE + 1<br />END<br /><br />--移除WITH ENCRYPTION<br />SET @REAL_DECRYPT_01 = REPLACE(@REAL_DECRYPT_01, 'WITH ENCRYPTION', '')<br />INSERT INTO [SQL_DECODE] VALUES (@REAL_DECRYPT_01)<br /><br />--PRINT '@REAL_DECRYPT_01 = ' + @REAL_DECRYPT_01<br />--PRINT 'LEN(@REAL_DECRYPT_01) = ' + STR(LEN(@REAL_DECRYPT_01))<br /><br />--删除原存储过程<br />SET @FACK_01 = 'DROP PROCEDURE ' + @PROC_NAME<br />EXEC(@FACK_01)<br /><br />GO</p>