Searching the MetaData of a SQL Server Database, Stored Procedure Text

This has been a favorite stored procedure of mine to search the text of my stored procedures for certain phrases... really helpful as the number of stored procs you have grows quite a bit:

 --xps '%procedure%' --search for the word 'procedure' in all your stored procs

CREATE

proc [dbo].[xpS]

@sSystemObjectText nvarchar(1000)

as

select

distinct sysobjects.name

from

sysobjects inner join syscomments on

sysobjects.id=syscomments.id

where

syscomments.text like @sSystemObjectText

order

by name