How to stop stored procs from whining about a missing column that I am about to delete in SQL Server 2008?

Question

I am deleting a column from one of the frequently used tables in my database. Last time I did this errors started to crawl up from all sorts of stored procs that I had long forgotten existed; complaining about the missing column. (only when that stored proc was called)

so, I dont want to get winded up with these things again, I want to make sure all stored procs are free of that column before I actually delete it.

What is the best way to search through all stored procs (and I have quite a lot of them) and remove the reference to that column?

I tried to find an option in the menu to do this but I did not find anything very obvious to do this.

any help, (other than telling me to go through them all one by one) is appreciated.
ps: of course, doesnt mean that I will depreciate your comment if you do tell me. I will only downvote :P
(nah, just kidding!)

Answer 1

To add to the various TSQL solutions, there is a free tool from Red Gate that integrates into SSMS: SQL Search

Answer 2

Use this script. It will also return triggers. If many tables has column with the same name you can add tale name to the where too. This script works on MSSQL 2000, 2005. I haven't tested it on 2008, but it should work fine too.

SELECT o.name
FROM sysobjects o
    INNER JOIN syscomments c ON o.id = c.id
WHERE c.text like '%column_name%'

Edit: If you want to filter it only to store procedures add AND type ='P' to the where clause