Stored Procedure Searches for Strings

I created a stored procedure named sp_ FindReferences.sql, which you can download from the SQL Server Magazine Web site.This stored procedure accepts an input string and searches the syscomments, sysobjects, and syscolumns system tables in the local database for any reference to that the string. The output result set shows all the tables, procedures, views, and other elements that contain the specified input string.

For example, I was recently asked to find out whether we use Province_Code or Province_ID in one of our company's databases. I used sp_FindReferences to perform a quick search. It turns out that the tables contain columns called Province_ID, yet the views to those tables remap the column to Province_Code.

I use sp_FindReferences almost daily. I hope you find this stored procedure useful, too.