在存储过程中查找和替换文本的SQL查询

How can I find and replace specific text in a stored procedure?

如何查找和替换存储过程中的特定文本?

I have the find and replace query but I'm not sure how to post that back to the stored procedure or update the stored procedure with the query result.

我有find和replace查询，但是我不确定如何将查询返回到存储过程中，或者使用查询结果更新存储过程。

Here is the query that I am using for the find and replace:

下面是我在查找和替换时使用的查询:

SELECT
   REPLACE (object_definition(object_id('storedprocedure_1')), 'findstring', 'replacestring')

2 个解决方案

#1

Declare @spnames CURSOR
Declare @spname nvarchar(max)
Declare @moddef nvarchar(max)
Set @spnames = CURSOR FOR
select distinct object_name(c.id) 
from syscomments c, sysobjects o 
where c.text like '%findtext%'
and c.id = o.id
and o.type = 'P'    
OPEN @spnames
FETCH NEXT
FROM @spnames into @spname
WHILE @@FETCH_STATUS = 0
BEGIN   
    Set @moddef =
    (SELECT
    Replace ((REPLACE(definition,'findtext','replacetext')),'ALTER','create')
    FROM sys.sql_modules a
    JOIN 
    (   select type, name,object_id
    from sys.objects b
    where type in (
    'p' -- procedures
    )
    and is_ms_shipped = 0
    )b
    ON a.object_id=b.object_id where b.name = @spname)              
    exec('drop procedure dbo.' + @spname)
    execute sp_executesql @moddef
    FETCH NEXT FROM @spnames into @spname
END

This is what I was able to come up with, its currently doing the text replace and re creating the stored procedures.

这就是我所能想到的，它目前正在执行文本替换和重新创建存储过程。

#2

You can get the definition of a stored procedure like so:

您可以得到存储过程的定义如下:

use <your database>
go
select
definition
from
sys.sql_modules
where
object_id = OBJECT_ID('<your schema.procedure_name>')

That will return the actual definition of the procedure you specify. You can build a list of stored procedures and walk through that one at a time, I guess. Not so sure I'd recommend trying to change them in sys.sql_modules, but at least you could find the ones that contain the text you're looking for.

这将返回您指定的过程的实际定义。我想，您可以构建一个存储过程列表，并一次遍历该过程。不太确定，我建议尝试在sys中修改它们。sql_modules，但至少您可以找到包含要查找的文本的模块。

#1

Declare @spnames CURSOR
Declare @spname nvarchar(max)
Declare @moddef nvarchar(max)
Set @spnames = CURSOR FOR
select distinct object_name(c.id) 
from syscomments c, sysobjects o 
where c.text like '%findtext%'
and c.id = o.id
and o.type = 'P'    
OPEN @spnames
FETCH NEXT
FROM @spnames into @spname
WHILE @@FETCH_STATUS = 0
BEGIN   
    Set @moddef =
    (SELECT
    Replace ((REPLACE(definition,'findtext','replacetext')),'ALTER','create')
    FROM sys.sql_modules a
    JOIN 
    (   select type, name,object_id
    from sys.objects b
    where type in (
    'p' -- procedures
    )
    and is_ms_shipped = 0
    )b
    ON a.object_id=b.object_id where b.name = @spname)              
    exec('drop procedure dbo.' + @spname)
    execute sp_executesql @moddef
    FETCH NEXT FROM @spnames into @spname
END