How to search for a string in your SQL Server database code

*****************************************************************
EDIT May 27, 2014: Forget this!  Really, just forget it and use the Red Gate SQL Search tool.  I just downloaded it and have been using it all morning. It's free, functional, searches EVERYTHING at the same time (job steps, tables, columns, etc), and has a very usable interface. This will replace ALL of my queries against system objects.

One tip - if you need to search different servers, you may find it difficult to switch database servers within the Red Gate SQL Search tool. The drop down doesn't appear to work. I've found that the only way to do that is to close the tool, go to the server you want in Object Explorer, and then open the tool again.  You will now be searching the correct server.
*****************************************************************

As anyone who's been a developer for any length of time knows, what you're doing most frequently is NOT working with a fresh slate on a brand new project. No, it's less fun but much more common that you'll be working on enhancing, fixing, modifying, or otherwise updating code that's already out there. And this is another thing you figure out very soon in your career - documentation for the average database product that's internal to a business is almost always either non-existent, or inaccurate.

So, a very important skill for the developer is the ability to research, and figure out what's out there already.

I like to use the following query to search for code. It will return all the code (including views, stored procedures, functions, etc) that contain the string (table name, view name, stored procedure name, etc) you're searching for. There are add-ons that have similar functionality, but I'm not always able to install an add-on.

A huge bonus with this query is the XML field that contains the definition. When you click on it, you can see the actual text of the definition in a new window - no sp_helptext needed!

 SELECT  
   Type =  
     case  
       when OBJECTPROPERTY(object_id, 'IsProcedure') = 1 then 'Procedure'  
       when OBJECTPROPERTY(object_id, 'IsView') = 1 then 'View'  
       when OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 then 'TableFunction'  
       when OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 then 'ScalarFunction'  
       else 'other'  
     end  
   ,Name = OBJECT_NAME(object_id)   
   ,Definition = CAST((SELECT definition AS [processing-instruction(x)] FOR XML PATH('')) AS XML)   
 FROM sys.sql_modules  
 WHERE  
   Object_definition(object_id) LIKE '%hier%'  
 order by 2, 1 

This is the output you'll get. Try clicking on the definition field to open it up in a new window.