Statements that encrypt stored procedures in batches

Statement [SQL] USE MyTargetDB go IF object_id ('wfp _ before_add_password_backup ') IS NULL SELECT o. name AS proc_name, s. definition INTO tempdb. dbo. wfp_before_add_password_backup FROM sys. SQL _modules s, sys. objects o WHERE o. object_id = s. object_id and s. definition is not null AND o. type = 'P' IF OBJECT_ID ('tempdb. dbo. new_definition_error ') is not null drop table tempdb. dbo. new_definition_err Or -- during execution, an error is collected to create table tempdb in this TABLE. dbo. new_definition_error (proc_name NVARCHAR (128), errmsg NVARCHAR (MAX), remark CHAR (1) IF OBJECT_ID ('tempdb. dbo. wfp_before_add_password_with_new_definition ') is not null drop table tempdb. dbo. wfp_before_add_password_with_new_definition SELECT o. name AS proc_name, s. definition, CAST (null as nvarchar (MAX) add_password_definition INTO tempdb. dbo. wfp_ B Efore_add_password_with_new_definition -- back up the pre-encryption process code to prevent unexpected FROM sys. SQL _modules s, sys. objects o WHERE o. object_id = s. object_id AND o. type = 'p' AND o. name LIKE 'pr _ % 'and s. definition is not null; -- remove DECLARE @ definition NVARCHAR (MAX), @ proc_name NVARCHAR (128), @ new_definition NVARCHAR (MAX) DECLARE @ newline VARCHAR (2) SET @ newline = CHAR (13) + CHAR (10) -- CURSOR. Each process processes DECLARE c cursor fo separately. R select proc_name, definition FROM tempdb. dbo. export OPEN c fetch next from c INTO @ proc_name, @ definition WHILE @ FETCH_STATUS = 0 BEGIN -- remove) AS process SET @ definition = REPLACE (SUBSTRING (@ definition, 1,500 ), ') AS', ') AS') + SUBSTRING (@ definition, 501,999 99999) DECLARE @ c1 NVARCHAR (MAX) --- parameter string SET @ c1 = '%' + (SELECT name + '%' FROM sys. parameters WHERE obje Ct_id = OBJECT_ID (@ proc_name) order by parameter_id asc for xml path ('')) -- PRINT '@ c1 =' + @ c1 --- locate the first parameter position: DECLARE @ position1 int select @ position1 = PATINDEX (@ c1, @ definition) -- PRINT: @ position1 = '+ CAST (@ position1 as varchar) DECLARE @ end_parameter NVARCHAR (128), @ len_end_parameter int set @ end_parameter = ISNULL (select top 1 name FROM sys. parameters WHERE object_id = OBJECT_ I D (@ proc_name) order by parameter_id DESC), '') DECLARE @ position_end int declare @ position_split INT -- print replicate ('*', 100) SET @ position_end = PATINDEX ('%' + @ end_parameter + '% AS %', @ definition) --- locate the position at the end of the last parameter SET @ position_end = @ position_end + LEN (@ end_parameter) --- determine the separation position SET @ position_split = CHARINDEX ('as', @ definition, @ position_end) SET @ new_definition = SUBSTRING (@ definition, 1, @ Position_split-1) + @ newline + 'with encryption' + @ newline + SUBSTRING (@ definition, @ position_split, 99999999) -- PRINT @ new_definition begin try ---- after a statement execution error is ensured, You can retain the original statement that cannot be replaced, add the begin tran new_definition IF @ new_definition is not null begin exec ('drop proc ['+ @ proc_name +'] ') -- first drop EXEC (@ new_definition) -- create update tempdb again. dbo. wfp_before_add_password_with_new_definition SET add_pa Ssword_definition = @ new_definition -- collect the newly defined statements, WHERE proc_name = @ proc_name end if @ new_definition is null -- NULL when an error occurs in the concatenation, new_definition_error to find the cause begin insert into tempdb. dbo. new_definition_error (proc_name, remark) SELECT @ proc_name, '1' end commit tran new_definition end try begin catch rollback tran new_definition insert into tempdb. dbo. new_definition_error -- roll back when an error occurs; Collect error information (proc _ Name, errmsg) SELECT @ proc_name, ERROR_MESSAGE () end catch fetch next from c INTO @ proc_name, @ definition end close c DEALLOCATE c -- there is an unencrypted process, forcibly returns the error if exists (SELECT * FROM sys. SQL _modules s, sys. objects o WHERE o. object_id = s. object_id AND o. type = 'p' AND o. name LIKE 'pr _ % 'and s. definition is not null) BEGIN select 'This encryption operation is not completely successful! Please manually execute encryption SELECT OBJECT_NAME (. object_id),. definition FROM wfp. sys. SQL _modules a, wfp. sys. objects B WHERE. definition is not null and B. name LIKE ''pr _ % ''AND. object_id = B. object_id AND B. type = 'P' view the failure process, and then manually operate 'as error_msg END