Find Out the Calling Stored Procedure in SQL Server

Find out the calling stored procedure in SQL Server

I would use an extra input parameter, to specify the source, if this is important for your logic.

This will also make it easier to port your database to another platform, since you don't depend on some obscure platform dependent function.

How can I find stored procedure calls?

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

So in your case, if you know what the stored procedure is called that you're interested in - just key that into the search box and SQL Search will quickly show you all the places where that stored procedure is being called from.

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

How to Check the calling stored procedure in SQL Server

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.

Is it possible to find out who called a stored procedure in SQL Server 2005

Use Adam Machanic's Who is Active stored procedure - this returns all sorts of info about active statements, including the user who launched them.

Find out who is executing the stored procedure from within said procedure

To achieve this you can use functions like

• SUSER_NAME() Function (it returns the login identification name of the user) MSDN article
• CURRENT_USER() (it returns the name of the current user) MSDN article
• USER_NAME() (it returns a database user name from a specified identification number) MSDN article
• ORIGINAL_LOGIN() (it Returns the name of the login that connected to the instance of SQL Server.) MSDN article

Example:

CREATE PROCEDURE
AS 
BEGIN
    DECLARE @executor 
    SELECT @executor = SUSERNAME()
    ...
END

You can read more in this very useful articles:

• Functions That Return User Names and User IDs

• Difference between ORIGINAL_LOGIN and SUSER_NAME

How to identify the caller of a Stored Procedure from within the Sproc

select hostname from master..sysprocesses where spid=@@SPID

or

select host_name from sys.dm_exec_sessions where session_id=@@SPID

How do I find the calling stored procedure/function

The Retrieve Job Information (QUSRJOBI) API can return the qualified name of the object that is running the current (most recent) SQL statement in a job. Use format JOBI0900 to access a job's SQL information.

You'd want to create an external stored procedure to call the API. (If you try to do it as a SQL stored proc, the problem would be that a statement in the SP itself would be the current one; and you want the current SQL to be in the calling proc.)

When this SP is called, the "current" SQL should be the CALL statement from back in the calling procedure. (I hope that makes sense.)

---

Related Topics