3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ramy
  • 20,541
  • 41
  • 103
  • 153
  • The problem with using syscomments is that the text column is nvarchar(4000) and you'll get truncation issues when the text splits between multiple rows. @KM's answer is the better way to go. – Joe Stefanelli Dec 20 '10 at 19:14
  • possible duplicate of [Find All References to View](http://stackoverflow.com/questions/1506082/find-all-references-to-view) – OMG Ponies Dec 20 '10 at 19:16

3 Answers3

9

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.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • 1
    +1 - You can use `sp_depends` too but it won't show inter-database dependencies. – JNK Dec 20 '10 at 19:19
5

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

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.

NotMe
  • 87,343
  • 27
  • 171
  • 245