Quick SQL Server tip : Find references to table or column names

Many times I have to look within a database and find all the programmable objects that refer to a particular column or table name (any string really).

There’s probably a slicker, more “politically correct” way to do this, but this is what I generally use to accomplish this:

SELECT DISTINCT ‘EXEC sp_helptext [‘ + OBJECT_NAME(id) + ‘]’ FROM syscomments WHERE charindex(‘soughtfortoken’, text) > 0

I’ve also got the output formatted to include the call to sp_helptext so that I can just paste this into a query window to see the source code that created the object. I suppose I could enhance this so it just executes via another call to EXEC, but this seems to do the job pretty well and requires no additional setup or installation.

Technically it is bad practice to query the sys* tables. Here’s the equivalent query using approved systems views:

SELECT DISTINCT ‘EXEC sp_helptext [‘ + OBJECT_NAME(object_id) + ‘]’ FROM sys.sql_modules WHERE charindex(‘soughtfortoken’, definition) > 0 ORDER BY 1

This entry was posted in SQL Server. Bookmark the permalink.

2 Responses to Quick SQL Server tip : Find references to table or column names

  1. This works very well to resolve the places where a particular column is referenced. Thank you for posting it. You saved me several hours of finagling.

  2. Tom Huguelet says:

    So glad you found it helpful !

Leave a Reply

Your email address will not be published. Required fields are marked *