The following is a running list of MS SQL scripts I've used to search for items within databases.  Wouldn't it be great if they just had a better Find function in SQL Management Studio?!

Find All Tables with Certain Text in the Table Name

use YourDataBaseNameHere
go

declare @StringToSearch varchar(100)
set @StringToSearch = '%' + 'SearchForThisTextHere' + '%'

select distinct st.name as 'table' from sys.columns sc
inner join sys.tables st on st.object_id = sc.object_id
where st.name like @StringToSearch
order by st.name

Find All Tables with Certain Text in Column Names

use YourDataBaseNameHere
go

declare @StringToSearch varchar(100)
set @StringToSearch = '%' + 'SearchForThisTextHere' + '%'

select st.name as 'table', sc.name as 'column', sc.column_id from sys.columns sc
inner join sys.tables st on st.object_id = sc.object_id
where sc.name like @StringToSearch
order by st.name

Find All Stored Procedures that Contain Certain Text

USE YourDatabaseNameHere
go

DECLARE @StringToSearch varchar(100)
SET @StringToSearch = '%' + 'SearchForThisTextHere' + '%'

Find All Stored Procedures that Contain Certain Text In Their Name

USE YouDatabaseNameHere
go

DECLARE @StringToSearch varchar(100)
SET @StringToSearch = '%' + 'SearchForThisTextHere' + '%'

SELECT DISTINCT SO.NAME
FROM SYSOBJECTS SO (NOLOCK)
WHERE SO.TYPE = 'P'
AND SO.NAME LIKE @StringToSearch
ORDER BY SO.Name

References:

1. Les Smith at http://www.knowdotnet.com/articles/storedprocfinds.html