Change all views and saved priorities, find and replace?

Question

Is there an easy way to find and replace 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

You need to look at sysobjects and syscomments, view text and stored procedures 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 cannot update system directories :( Therefore, the only easy way to do this is to use the Script generator, and then search and replcae in your favorite text editor.

EDIT: Script, ALTER, looong syscomments .. .. .

Answer 2

:

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

, , .

data​​strike > .

script "Drop and Create to" File . "Notepad ++" . script . , .:)

Answer 3

Management Studio script , " " " " " "

" "

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

" "

SET QUOTED_IDENTIFIER \1\nGO\nALTER \2

This will create an ALTER script for all stored procedures and views. You can then do a standard search and replace for "X United Kingdom" with "X (UK)" and execute the script.