Edit all views and stored precedures, find and replace?

Is there an easy way to do a find and replace on a string in every view and stored procedure in my SQL Server database. I need to replace something l开发者_如何学Goike 'X United Kingdom' with 'X(UK)'.

You need to look at sysobjects and syscomments, the text of views and stored procedures are in syscomments. Their types V = View and P = Procedure are in sysobjects

/*Search Stored Procedure and View Text*/  
declare @searchString varchar(100)

SELECT @searchString = 'X United Kingdom' 

SELECT Distinct   
    SO.Name,  SC.[text]  
FROM   
    sysobjects SO (NOLOCK)  
    INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID  
        AND SO.Type IN ('P', 'V')  
        AND SC.Text LIKE '%' + @searchString + '%'

Unfortunately you can't update system catalogs :( So the only easy way to do it is to use the Script Generator and then search and replcae in your favourite text editor.

EDIT: I had posted a script that generated ALTER statements, but truncation of a looong procedure and that syscomments is deprecated etc... etc.. I rescinded this part of my answer.

Found a way to do it automatically, change the "o.type" accordingly to your need (P = SQL Stored Procedure, U = Table (user-defined), V = View... reference: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver15 )

DECLARE @queryDef NVARCHAR(max)

WHILE EXISTS (
                    SELECT 1
                    FROM sys.sql_modules    sm 
                    JOIN    sys.objects  o ON sm.object_id = o.object_id 
                    WHERE   sm.definition LIKE '%TEXT_TO_REPLACE%' 
                    AND o.type = 'V'
            )
BEGIN
    
                                                                    -- TO ALTER THE VIEW AUTOMATICALLY                                          
    SET @queryDef = (   SELECT TOP 1 Replace (Replace (sm.definition, 'CREATE VIEW', 'ALTER VIEW'), 
                                            'TEXT_TO_REPLACE', 
                                            'NEW_TEXT') 

                        FROM sys.sql_modules    sm 
                        JOIN    sys.objects  o ON sm.object_id = o.object_id

                        WHERE   sm.definition LIKE '%TEXT_TO_REPLACE%'
                        AND     o.type = 'V')
    
    EXEC (@queryDef)

END

Here is how I do this:

SELECT distinct so.Name
from sysobjects as so 
inner join syscomments as sc 
on so.id = sc.id
AND so.Type = 'P'
AND sc.Text LIKE '%foo%'
ORDER BY so.Name

here is query to find stored procedure containing specific text.

Create backup of data database.

Later I generate script "Drop and Create to" File using management studio. and use "Notepad++" to replace strings. Execute script again. Not better way, but works for me. :)

In Management Studio you can script all stored procedures, and views to a new query window then in the "Find and Replace" dialogue select "case insensitive" and "use regular expressions"

In the "Find What" enter

SET QUOTED_IDENTIFIER {(ON|OFF)}\nGO[:b\n]+CREATE[:b\n]+{(PROC|VIEW)}

In the "Replace with" enter

SET QUOTED_IDENTIFIER \1\nGO\nALTER \2

This will generate an ALTER script for all stored procedures, and views. Then you can do a standard find and replace for 'X United Kingdom' with 'X(UK)' and execute the script.