Query to find out the stored procedure depending on a column

Question

I have to change the name and the datatype of a column of a table. I have about 150 stored procedures in the database, out of which about 25 refer to the same column. I need a query that can find the name of all the stored procedures which are dependent on this column.

Answer 1

I use this query:

SELECT OBJECT_NAME(M.object_id), M.*
FROM sys.sql_modules M
JOIN sys.procedures P
ON M.object_id = P.object_id
WHERE M.definition LIKE '%blah%'

Obviously you'd have to substitute "blah" for the name of your column.