How to Check the calling stored procedure in SQL Server

I have a Stored proc Name example “mySp”, I would like to know who call this stored Proc, I mean this stored Proc (mySp) is being called by some other stored Proc, please let me know how do I check who is calling my stored proc in SQL server. I have around 1500 stored proc in my sql servers.

Answer

In SQL Server Management Studio you can right click on your mySP and choose “View Dependencies” The code that gets executed in the background is:

SELECT SCHEMA_NAME(sp.schema_id) AS [Schema], sp.name AS [Name]
FROM sys.all_objects AS sp
WHERE (sp.type = 'P' OR sp.type = 'RF' OR sp.type='PC')
and(sp.name='yourSPname' and SCHEMA_NAME(sp.schema_id)='yourSchema')

Where yourSPname would be mySp and yourSchema like dbo.