给大家分享好东本喽！将当前数据库里所有存储过程进行批量替换方案

最新推荐文章于 2024-07-07 19:29:04 发布

iteye_14608

最新推荐文章于 2024-07-07 19:29:04 发布

阅读量166

点赞数

文章标签：数据库

----因业务需要，可能会将存储过程中的某一部分内容进行替换。比方说，“记账”与“记帐”，要进行全部统一，以下脚本提供了解决方案

USE [MyTargetDB]
go 
--DROP TABLE all_proc_before_replace,to_replace_create_proc_error
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[_]crm[_]%' --剔除掉CRM相关过程
			AND s.definition IS NOT NULL --未加密的过程
go
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[_]crm[_]%' --剔除掉CRM相关过程
        
        OPEN c 
        FETCH NEXT FROM C INTO @proc_name, @definition 
        WHILE @@FETCH_STATUS = 0 
            BEGIN 
		
                IF @definition LIKE '%tempdb%' --符合条件的过程，进行替换
                    BEGIN 
                        SET @definition = REPLACE(@definition, '记账', '记帐')
                        -- 此处可以进行多次 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 ' 本服务器WFP数据库中，有过程对象未替换完成，请手工处理 '
            END