How to find all instances of a procedure (or function) in SQL Server 2005/2008

Question

I changed the procedure, and now it takes more parameters. How can I find every place that the procedure calls, so I can update the number of arguments passed to proc?

I tried this:

select * from syscomments where text like '%MODIFIED-PROCEDURE-NAME%'

but I still find other places proc called that this request did not return.

Answer 1

use sys.sql_modules:

SELECT
    OBJECT_SCHEMA_NAME(m.object_id) + '.' + OBJECT_NAME(m.object_id)
    FROM sys.sql_modules  m
    WHERE m.definition like '%whatever%'

sys.sql_modules.definition - nvarchar (max). Other similar views have nvarchar (4000) columns, where the text is divided into several lines.

Answer 2

Red-Gate SQL Search - , , . , , ! , - !

Answer 3

If this is all inside the SQL server, you can simply recompile it.

Just create a single script containing all stored procedures and functions. Run the script. It will bomb where the problems are.

Optionally, you can simply find the generated script.