Encrpt all Stored Procedures.

CREATE FUNCTION [dbo].[fncGetAsIndex](@searchText nvarchar(max))RETURNS intASBEGIN
DECLARE @asIndex_current int,@asIndex_total int declare @preChar char(1),@postChar char(1)
set @asIndex_current = 0 ; set @asIndex_total = 0
while (charindex ('as',lower(@searchText)) > 0 )
begin
set @asIndex_current = charindex ('as',lower(@searchText)) set @asIndex_total = @asIndex_total + @asIndex_current + case when @asIndex_total=0 then 0 else len('as')-1 end
set @preChar = substring(@searchText,@asIndex_current - 1 , 1) set @postChar = substring(@searchText,@asIndex_current + len('as') ,1) if (@preChar = '' or @preChar = ')' or @preChar = char(10) or @preChar = char(9))
begin
if (@preChar = '' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = ')' and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(10) and ( @postChar = '' or @postChar = char(13) or @postChar = char(9)) )
return @asIndex_total
if (@preChar = char(9) and ( @postChar = '' or @postChar = char(13)) )
return @asIndex_total
end
set @searchText = substring(@searchText,@asIndex_current + len('as') ,len(@searchText) - @asIndex_current)
end
RETURN 0
END




----------------------------------------------------

CREATE FUNCTION [dbo].[fncGetCreateIndex](@type varchar(2),@searchText nvarchar(max))RETURNS intASBEGIN
declare @typeStatement varchar(10)
if @type = 'P' set @typeStatement = 'PROC' else set @typeStatement = 'FUNCTION' DECLARE @createIndex_current int,@createIndex_total int set @createIndex_current = 0 ; set @createIndex_total = 0
while (charindex ('create',lower(@searchText)) > 0 )
begin
set @createIndex_current = charindex ('create',lower(@searchText)) set @createIndex_total = @createIndex_total + @createIndex_current + case when @createIndex_total=0 then 0 else len('create')-1 end
if (lower(left(ltrim(rtrim(substring(@searchText,@createIndex_current + len('create'),len(@searchText)))),len(@typeStatement))) = lower(@typeStatement))
return @createIndex_total
set @searchText = substring(@searchText,@createIndex_current + len('create') ,len(@searchText) - @createIndex_current) end
RETURN 0
END




------------------------------------------------------------------------

CREATE proc [dbo].[NwdicEncrypt]@type varchar(2),@physicalTableName nvarchar(100)
as
begin
IF OBJECT_ID('tempdb..#backup') IS NOT NULL DROP TABLE #backup
CREATE TABLE #backup ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL )
DECLARE @sptexttable TABLE ( id BIGINT IDENTITY(1, 1), spname NVARCHAR(100), sptext NVARCHAR(MAX) )
INSERT INTO @sptexttable (spname , sptext) select [name], object_definition(object_id) as [text] from sys.objects where [type]=@type and object_definition(object_id) is not null and [name] not in ('NwdicEncrypt','fncGetAsIndex')
DECLARE @sptext NVARCHAR(MAX), @spname NVARCHAR(max)DECLARE @errMessage NVARCHAR(MAX)DECLARE @counter BIGINT
SET @counter = 1
WHILE (@counter <= ( SELECT MAX(id) FROM @sptexttable )) BEGIN
INSERT INTO #backup ( spname , sptext) SELECT spname, sptext FROM @sptexttable WHERE id = @counter
DECLARE @tempproc NVARCHAR(MAX) DECLARE @procindex INT DECLARE @newProc NVARCHAR(MAX) DECLARE @newheader NVARCHAR(MAX) DECLARE @header NVARCHAR(MAX) DECLARE @asindex INT DECLARE @replacetext NVARCHAR(MAX)
select @tempproc=sptext FROM @sptexttable WHERE id = @counter
BEGIN TRY
set @procindex = dbo.[fncGetCreateIndex](@type,@tempproc)
set @asindex = dbo.fncGetAsIndex(@tempproc)
select @spname = spname FROM @sptexttable WHERE id = @counter
set @header = SUBSTRING(@tempproc, @procindex, @asindex - @procindex)
set @newheader = 'ALTER ' + SUBSTRING(@header,LEN('create') + 1 ,len(@header) ) set @newheader = STUFF(@newheader, len(@newheader), 1, CHAR(13) + 'WITH ENCRYPTION' + CHAR(13))
set @newProc = REPLACE(@tempproc, @header, @newheader)
BEGIN TRY
begin EXEC ( @newProc ) UPDATE #backup SET encrypttext = @newProc, encryptstatus = 1 WHERE id = @counter end
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
END TRY BEGIN CATCH
begin set @errMessage = error_message() UPDATE #backup SET encrypttext = @newProc, encryptstatus = 0 , errMessage = @errMessage WHERE id = @counter end
END CATCH
SET @counter = @counter + 1
END exec ('IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @physicalTableName + ']'') AND type in (N''U''))DROP TABLE [dbo].[' + @physicalTableName + ']
create table ' + @physicalTableName + ' ( id BIGINT , spname NVARCHAR(100) NOT NULL, sptext NVARCHAR(MAX) NOT NULL, encrypttext NVARCHAR(MAX) NULL, encryptstatus BIT NOT NULL DEFAULT ( 0 ), errMessage NVARCHAR(max) NULL ) insert into ' + @physicalTableName + ' select * from #backup'
)
end