Quickest/Easiest Way to Use Search/Replace Through All Stored Procedures

Quickest/Easiest way to use Search/Replace through all stored procedures

To search: if you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use?

This tool doesn't support replacing text, however - but even just being able to find all the relevant stored procedures (or other DB objects) is very helpful indeed!

How do I find a stored procedure containing text?

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

---

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Foo%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

---

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

How to replace the hardcoded data in views, stored procedures, and functions via a script?

Assuming the procedure starts with CREATE exactly, you can say:

SELECT STUFF(REPLACE(definition, '1, 2, ...', 'SELECT ...'), 1, 6, 'ALTER') 
    + CHAR(13) + CHAR(10) + 'GO'
FROM sys.sql_modules WHERE definition LIKE '%(1, 2, ...')%';

If it can have leading spaces, comments before CREATE, etc. then it can get a bit more complex.

You can also just return the scripts themselves, or script them out to files, then do your own search and replace within SSMS or your favorite text editor.

EDIT note that this will just produce the script to alter your objects. You'll want to verify them, backup your database first, then copy the script and run it.

EDIT 2 explaining code based on question

sys.sql_modules.definition contains a CREATE script that represents your procedure/function body. It does not include the GO required between altering modules, nor is there a way to have it output an ALTER command instead (I've asked for it in the past but I'll have to agree with them I'd rather see CREATE OR REPLACE syntax).

STUFF in this case takes the output (which will be CREATE PROCEDURE ... after the 1, 2, ... has been replaced) and, starting with the first character, replaces the first 6 characters (CREATE) with ALTER - since the assumption is that you will want to run these as alters as opposed to drop / create (so that you don't lose permissions / dependencies).

The GO adds a batch separator between each body, since ALTER PROCEDURE must be in its own batch. So instead of...

CREATE PROCEDURE dbo.foo
AS
BEGIN
    ...
END

CREATE PROCEDURE dbo.bar
AS
BEGIN
    ...
END

...which will generate errors, you'll have...

CREATE PROCEDURE dbo.foo
AS
BEGIN
    ...
END
GO
CREATE PROCEDURE dbo.bar
AS
BEGIN
    ...
END
GO

...which will ensure that each stored procedure is handled in its own batch.

---

Related Topics