May 30, 2007 at 7:56 am
How can i find a column name from all the SP's in a database, I mean where ever that column is used in a store proc, i need a list of all those store procs.
May 30, 2007 at 9:49 am
This should work...
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%mycolumn%'
May 30, 2007 at 10:08 am
I found out that the routine provided by Aaron does not always return all the SPs where the specific column name is mentioned so you might want to try this. One thing to be aware of is that both methods might return false positives if your column name is used as a portion of another column name. For example if you looking for a column Name and you have columns FirstName and LastName all SPs with any of the three columns will be displayed.
SELECT DISTINCT SO.name
FROM sysobjects SO
INNER JOIN syscomments SC ON SC.ID = SO.ID
WHERE SC.text LIKE '%column_name%'
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply