Query the contents of stored procedures on SQL Server

Question

I am exploring a legacy database system and have very little knowledge of its internals. I would like to find all the stored procedures that invoke another stored procedure A.

How best to do this?

Can I write something like this pseudocode:

select name from AllStoredProcedures as Asp where Asp.TextualContent contains 'A' 

Asp.TextualContent means the actual SQL contained in the SP.

Answer 1

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

Answer 2

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