How to search all sprocs and views?

Execute the following Microsoft SQL Server Transact-SQL scripts to demonstrate searching all sprocs and views for keywords such as table name, column name, database name, etc.. 

use AdventureWorks2008;

-- Search all stored procedures

select StoredProcedureName

=convert(sysname,SCHEMA_NAME(schema_id))+'.'+name

from sys.objects where type = 'P'

and PATINDEX ('%HumanResources%', OBJECT_DEFINITION (object_id)) > 0

go

-- Search all views

select ViewName

=convert(sysname,SCHEMA_NAME(schema_id))+'.'+name

from sys.objects where type = 'V'

and PATINDEX ('%Store%', OBJECT_DEFINITION (object_id)) > 0

go