Edit all views and stored precedures, find and replace?

Question

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 like 'X United Kingdom' with 'X(UK)'.

Answer 1

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. :)

Answer 2

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.

Answer 3

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.