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
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
No comments:
Post a Comment