Query the contents of stored procedures on SQL Server

SELECT OBJECT_NAME(object_id),
       definition
FROM sys.sql_modules
WHERE objectproperty(object_id,'IsProcedure') = 1
  AND definition    like '%Foo%' 

For SQL Server 2005/2008:

SELECT  s.name SchemaName
        ,o.name RoutineName
        ,o.[type] RoutineType
        ,procs.*
FROM    sys.sql_modules procs
INNER JOIN sys.objects o ON procs.object_id = o.object_id 
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE   procs.[definition] LIKE '%A%'
--AND       o.[type] = 'P' --'P' for stored procedures