T-SQL Searching through the schema

For SQL Server 2005:

---

Some examples:
select * from sys.objects
select * from sys.sql_modules <-- Stored Procedures and Views
select * from sys.columns
select * from sys.triggers


Searching the text of all stored procedures:
select o.name from sys.sql_modules m
inner join sys.objects o
on m.object_id = o.object_id
where m.definition like '%string you are looking for%'

Searching for tables that contain the column (field) you want:
select a.name
from sys.objects a
inner join sys.columns b
on a.object_id = b.object_id
where b.name='column name you are looking for'

For SQL Server 2000:

---

Searching the text of all stored procedures:
select object_name(id)
from syscomments
where objectproperty(id,'IsProcedure') = 1
and [text] like '%string you are looking for%'