Search DB for all Code that References a Table
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 14 Sep 2006
It always amazes me how, after so long as a DBA, I can still come across things and wonder 'how did I ever not know that?'
Today's realisation comes in the form of a system view sys.all_sql_modules, which is a concatenation of two other system views and which is a user-friendly listing of all code objects in a database (SPs, functions, views etc).
I suppose it was laziness on my part, as the deprecated syscomments view still works, even in SQL 2008R2, so I've never had to upgrade that part of my knowledge.
Anyway, this piece of code is a quick and dirty search through all code definitions returned by this view. It's not an intelligent search, so it will return instances where variable names or comments contain the search string, but intelligent construction of the search string can help to avoid false positives.