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.
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
