CODEWITHSUNDEEP
sql-server
Ramy
Ramy

Reputation: 21261

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 return.

Upvotes: 3

Views: 2793

Answers (3)

ChrisLively
ChrisLively

Reputation: 88092

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.

Upvotes: 0

marc_s
marc_s

Reputation: 755491

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!

alt text

Upvotes: 5

KM.
KM.

Reputation: 103707

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.

Upvotes: 9

Related Questions