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

I modified a procedure and it now takes a greater number of parameters. How can I find every place that the procedure is called so I can update the number of arguments the proc is passed?

I tried this:

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

but I'm still finding other places the proc is called that this query did not ret开发者_开发知识库urn.

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 is nvarchar(max). Other similar views have nvarchar(4000) columns, where the text is split over multiple rows.

Get yourself Red-Gate SQL Search - it's great, it's FREE and it just works. It can be used to do exactly what you're looking for! Go grab it - it's worth its weight in gold!

If this is all inside of SQL server you could just recompile it.

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

Optionally, you could just search the script you created as well.