It’s easy to find what calls a stored procedure in SQL Server. Finding where it is called outside of SQL Server is an entirely different matter. I use great free utilities for either use case.
Of course, you can use built-in features in SSMS to find out what calls a stored procedure, for example. Select an object in the Object Explorer, right-click, select View Dependencies.
Figure 1. View Dependencies in SSMS
You can also script a solution, either in T-SQL or PowerShell. Dependencies are found in sys.sysdepends in SQL Server 2005 and above or sysdepends in SQL Server 2000. What I really like to use is the free SQL Search utility from Red Gate. It’s powerful, intuitive, works well, and free (just in case you missed that very important point).
Things get more challenging when you need to know what items outside of SQL Server are referencing SQL Server objects. If you have .NET code, you can search all of your code using Visual Studio. But you might not have Visual Studio and even if you do, you could have a large collection of batch jobs (could be non-Microsoft) scattered around your filesystem as I experienced today.
My favorite free tool for searching the filesystem is FileLocator Lite. I’ve been using it for many years, but didn’t blog about it when I first discovered it because it had an awful name – Agent Ransack. Try telling your software compliance people that you want some freeware named Agent Ransack – that’s just not going to happen. Fortunately, this great tool was rebranded as FileLocator Lite.
I had to search all of the many files on a large network share for all of the stored procedures in a particular database. First I got a list of the names of all of the stored procedures.
where type = 'P';
The list looked something like this:
FileLocator Lite can search for multiple strings at once, provided they are separated by the correct Boolean operator, which is an OR in my case. It appeared to me that Boolean operators need to be in all uppercase to work properly. I used regular expressions with SSMS’s Search and Replace option to quickly edit my long list in a single pass.
Figure 2. Global search and replace using regular expressions.
I substituted OR \n for \n and ended up with something like this which I pasted into FileLocator Lite:
Figure 3. FileLocator Lite search results.
FileLocator Lite will search subfolders if you check the box to do so. It also supports regular expressions. What I find particularly useful is the context sensitive pane on the right where you can preview and see if the found string really is what you want. You can save your search criteria or your results to a file.