Wednesday, 8 October 2014
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.
Grumpy about: SQL script