If you do any amount of SQL Server development, at some point in time you've come across a situation where you need to search your database for all occurrences of a table, column or some specific SQL statement that might be used in a view, stored procedure, trigger etc. In the past I've ended up just dump the db schema to a SQL file and searched the file manually, but that's pretty slow and not very elegant.
This morning though I found a better way using Red Gate's SQL Search, which is a free add-in for Microsoft SQL Server Management Studio. This add-in (which is currently free, but will eventually be integrated into the commercial SQL Prompt add-in) allows you to quickly search for strings within your database schema. What's really nice is double-clicking on a matching object takes you right to the object in the Object Explorer—which can save you a lot of time when you have a really large database (I work in an application w/several hundred tables!)
If you use SQL Server Management Studio, I highly recommend you download SQL Search and try it out.
NOTE:If you're using Microsoft SQL Server Management Studio Express version, or you're "The Add-in 'SQL Search 1.0' failed to load or caused an exception. (Error number: 80070002)" when trying to start Management Studio after installing the add-in, you'll need the extensibility.dll installed before the add-in will work. You'll need to un-installing SQL Search, then download and install extensibility.dll and then re-install SQL Search.