Find that object with sp_find

Personally I don't always trust the SQL depencies report, so I compiled a basic proc that will search the current database for a specific string.

This string could be a proc name, or field name - and this search will return a list of objects in the current database that contain this text string anywhere in their structure (e.g. in a table column, in a view script, in a stored proc).  It also checks the jobs on the server for the string as well - useful if trying to track down where a stored proc is being called from.

Code link: sp_find

Create this in the master database and you can run it in the context of whichever database you are currently connected to.

I find it helpful to map it to a function key in SSMS, then just highlight the text I want to find, and press the relevant key.  Doesn't always work - e.g. if there is a space or a "." in the string - in which case wrap it in single quotes.

If you want to search all databases, you can use this wrapper proc, that cycles through all objects in all databases - so obviously it will take a bit longer.

Code link: sp_find_anydb

Both of them have an @IgnoreCommentedOut parameter, which also attempts to strip out any commented text from proc code (either via the '--' prefix, or contained within /* and */ identifiers).  Again this will slow down the code, so is not applied by default.