Query the contents of stored procedures on SQL Server

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 proce开发者_开发技巧dure 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.

---

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

---

This query will retrieve the textual definition of stored procedures and filter using a simple wildcard.

For 2000 (untested, but IIRC it's the right table):

select p.[type]
      ,p.[name]
      ,c.[text]
  from sysobjects p
  join syscomments c
    on p.object_id = c.id
 where p.[type] = 'P'
   and c.[text] like '%foo%'

For 2005:

select p.[type]
      ,p.[name]
      ,c.[text]
  from sys.objects p
  join sys.syscomments c
    on p.object_id = c.id
 where p.[type] = 'P'
   and c.[text] like '%foo%'

For 2005 and 2008+

select p.[type]
      ,p.[name]
      ,c.[definition]
  from sys.objects p
  join sys.sql_modules c
    on p.object_id = c.object_id
 where p.[type] = 'P'
   and c.[definition] like '%foo%'

---

Try This only one statement can solve your problem..

--note this does not show /r/n, it comes out as one long line
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.myStoredProc'))

or

DECLARE @objname nvarchar(max); -- note this truncates
SELECT @objname= OBJECT_DEFINITION(OBJECT_ID(N'dbo.myStoredProc'))
print @objname