CODEWITHSUNDEEP
sql-servert-sqlssms
chuckd
chuckd

Reputation: 14530

How can I search for stored procedures executing other stored procedures?

I have a list of about 350 stored procedures like this:

usp_SP1,
usp_SP2
...

I want to search through each one looking to see if any of them call other stored procedures or other databases?

I guess I would look for a line like 'exec something' in each one or a specific name of a database. ex. some_other_database

How would I do this to give me a list of the stored procedures that call other stored procedures or contain some specific string? ex. "some other database name"

I can run this below but it finds just the text. is there any way I can ensure it's a exec call and not just text?

USE [Your_DB];
GO

SELECT 
    ROUTINE_NAME, ROUTINE_DEFINITION
FROM 
    INFORMATION_SCHEMA.ROUTINES 
WHERE 
    ROUTINE_DEFINITION LIKE '%exec %' 
    AND ROUTINE_TYPE = 'PROCEDURE'
    AND ROUTINE_NAME IN ('usp_SP1', 'usp_SP2')
GO

Upvotes: 0

Views: 958

Answers (2)

JBJ
JBJ

Reputation: 403

Not to compete, Razvan Socol is correct. Adding the ways to do it in earlier version back to 2000. All the old tables and views are still query-able even if not visible in ssms. 

select distinct /* I believe the sys.sys views were added in 2012 or so, still works in 2017  */
     od.name caller_procedure_name
    ,o.name called_procedure_name
from sys.sysdepends d
inner join sys.sysobjects o on o.id = d.depid and o.type = 'P'
inner join sys.sysobjects od on od.id = d.id and od.type = 'P'

select distinct /* should work all the way back to sql 2000, still works in 2017 */
     od.name caller_procedure_name
    ,o.name called_procedure_name
from dbo.sysdepends d
inner join dbo.sysobjects o on o.id = d.depid and o.type = 'P'
inner join dbo.sysobjects od on od.id = d.id and od.type = 'P'

Upvotes: 0

Razvan Socol
Razvan Socol

Reputation: 5684

You can query the sys.sql_dependencies view, like this:

SELECT o1.name AS CallerSP, o2.name AS CalledSP
FROM sys.sql_dependencies sd
INNER JOIN sys.objects o1 ON o1.object_id = sd.object_id
INNER JOIN sys.objects o2 ON o2.object_id = sd.referenced_major_id
WHERE o1.type='P' AND o2.type='P'

You may need to call sp_refreshsqlmodule for all objects before executing this query, if the called SP was created after the caller.

Other options could be to query the sys.sql_expression_dependencies view or the sys.dm_sql_referenced_entities function.

Upvotes: 3

Related Questions