将存储过程批量进行加密的语句
[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_error
--执行中,出现错误的收集到本表中
CREATE TABLE tempdb.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_before_add_password_with_new_definition -- 将加密前的过程代码,备份;以防不测
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 ;--加密的过滤除去
DECLARE @definition NVARCHAR(MAX) ,
@proc_name NVARCHAR(128) ,
@new_definition NVARCHAR(MAX)
DECLARE @newline VARCHAR(2)
SET @newline = CHAR(13) + CHAR(10)
--游标,每个过程单独处理
DECLARE c CURSOR
FOR
SELECT proc_name ,
definition
FROM tempdb.dbo.wfp_before_add_password_with_new_definition
OPEN c
FETCH NEXT FROM c INTO @proc_name, @definition
WHILE @@FETCH_STATUS = 0
BEGIN
--将)AS 处理掉
SET @definition = REPLACE(SUBSTRING(@definition, 1, 500), ')AS', ') AS') + SUBSTRING(@definition, 501, 99999999)
DECLARE @c1 NVARCHAR(MAX) ---参数串
SET @c1 = '%' + ( SELECT name + '%'
FROM sys.parameters
WHERE object_id = OBJECT_ID(@proc_name)
ORDER BY parameter_id ASC
FOR
XML PATH('')
)
--PRINT '@c1 = ' + @c1 ---找到第一个参数位置
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_ID(@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)
--- 定位到最后一个参数末尾位置
SET @position_end = @position_end + LEN(@end_parameter)
--- 确定分隔位置
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
----保证语句执行错误后，可以保留原不能替换语句，加事务
BEGIN TRAN new_definition
IF @new_definition IS NOT NULL
BEGIN
EXEC (' drop proc [' + @proc_name + ']' )--先DROP
EXEC (@new_definition) --再CREATE
UP
