将当前数据库里所有存储过程的内容进行批量替换方案

将当前数据库里所有存储过程里的内容进行批量替换方案

1. 备份存储过程

USE [MyDB]  
go   
 
IF OBJECT_ID('master..all_proc_before_replace', 'U') IS NOT  NULL   
    DROP TABLE master..all_proc_before_replace  
  
    SELECT  o.name AS proc_name,  
            definition ,  
            o.type ,  
            ' ' AS remark  
    INTO    master..all_proc_before_replace   --- 做备份,备份到master..all_proc_before_replace   
    FROM    sys.sql_modules s ,  
            sys.objects o  
    WHERE   o.object_id = s.object_id  
            AND o.type = 'P' -- 只指定过程  
            AND o.name not  LIKE 'pr[_]fm[_]%' --剔除掉含fM相关过程  
            AND s.definition IS NOT NULL --未加密的过程  
go 

1.  替换

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON   
GO  
--创建表to_replace_create_proc_error 收集执行替换后的过程报错信息   
IF OBJECT_ID('master..to_replace_create_proc_error', 'U') IS NOT  NULL 
    DROP TABLE master..to_replace_create_proc_error  
CREATE TABLE master..to_replace_create_proc_error
    (
      proc_name NVARCHAR(128) ,
      definition NVARCHAR(MAX) ,
      errormsg NVARCHAR(MAX)
    )  
  
  
  
  
DECLARE @proc_name NVARCHAR(128) ,
    @definition NVARCHAR(MAX)   
DECLARE c CURSOR FAST_FORWARD  
FOR  
SELECT  proc_name ,  
definition  
FROM    master..all_proc_before_replace   
WHERE  proc_name NOT  LIKE 'pr[_]fm[_]%'
 --剔除掉停职fm相关过程  
          
OPEN c   
FETCH NEXT FROM C INTO @proc_name, @definition   
WHILE @@FETCH_STATUS = 0 
    BEGIN   
          
        IF @definition LIKE '%tempdb%' --符合条件的过程，进行替换  
            BEGIN   
                SET @definition = REPLACE(@definition, '表名', 'TableName')  
                        -- 此处可以进行多次 set @definition = REPLACE(@definition, '一', '壹')  
                        --SET @definition = REPLACE(@definition, '中心', 'tempdb..#')  
                        --SET @definition = REPLACE(@definition, 'tempdb..#WFPUSER#', 'master..#WFPUSER#')  
                BEGIN TRY   
                    BEGIN TRANSACTION drop_create_proc    
                    EXEC ( ' DROP PROC [' + @proc_name +']' ) -- 先DROP  
                    EXEC ( @definition )-- 再CREATE  
  
                    UPDATE  master..all_proc_before_replace -- 将已经替换后执行成功的，进行标记     
                    SET     remark = 'Y'
                    WHERE   proc_name = @proc_name   
                    COMMIT TRANSACTION drop_create_proc  
          
                END TRY   
                BEGIN CATCH   
                    ROLLBACK TRANSACTION drop_create_proc  
                    INSERT  INTO master..to_replace_create_proc_error   --将已经替换后执行不成功的，收集报错信息内容  
                            SELECT  @proc_name ,
                                    @definition ,
                                    ERROR_MESSAGE()  
                END CATCH   
            END   
        FETCH NEXT FROM C INTO @proc_name, @definition   
         
    END   
          
CLOSE c  
DEALLOCATE c   
  
  
  
IF EXISTS ( SELECT  *
            FROM    master..to_replace_create_proc_error ) 
    BEGIN   
                ---- 返回报错，便于查找  
        RAISERROR 999999 ' 本数据库中，有过程对象未替换完成，请手工处理 '  
    END