Current executing procedure name

Question

Is it possible to get the name of the current Stored Procedure in MS SQL Server?

Maybe there is a system variable or function like GETDATE()?

Solution 1

You may try this:

SELECT OBJECT_NAME(@@PROCID)

Update: This command is still valid on SQL Server 2016.

Solution 2

OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID)

Solution 3

You can use OBJECT_NAME(@@PROCID)

Returns the object identifier (ID) of the current Transact-SQL module. A Transact-SQL module can be a stored procedure, user-defined function, or trigger.

Solution 4

In the specific case where you are interested in the name of the currently executing temporary stored procedure, you can get it via:

select name
from tempdb.sys.procedures
where object_id = @@procid

You cannot use the accepted answer in SQL Server to find the name of the currently executing temporary stored procedure:

create procedure #p
as
select object_name(@@PROCID) as name
go
exec #p


name
--------------------------------------------------------------------------------------------------------------------------------
NULL

(1 row affected)

Solution 5

I know this is old, but this is what I use. It appears to always work.

BEGIN TRAN 
GO 
-- Stored procedure, function of trigger
CREATE PROC dbo.TempProc AS 
    DECLARE @ DATETIME = GETDATE(), @Me VARCHAR(64) = COALESCE(OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()), OBJECT_SCHEMA_NAME(@@PROCID, DB_ID('tempdb')),'session')+'.'+COALESCE(OBJECT_NAME(@@PROCID, DB_ID()) , OBJECT_NAME(@@PROCID, DB_ID('tempdb')),'SQL')
    SELECT ProcName = @Me
GO
EXEC dbo.TempProc 
GO
ROLLBACK 
GO
BEGIN TRAN 
GO
-- Temp Stored procedure
CREATE PROC #TempProc AS 
    DECLARE @ DATETIME = GETDATE(), @Me VARCHAR(64) = COALESCE(OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()), OBJECT_SCHEMA_NAME(@@PROCID, DB_ID('tempdb')),'session')+'.'+COALESCE(OBJECT_NAME(@@PROCID, DB_ID()) , OBJECT_NAME(@@PROCID, DB_ID('tempdb')),'SQL')
    SELECT ProcName = @Me
GO
EXEC #TempProc 
GO
ROLLBACK
GO
-- SSMS or direct SQL statement
DECLARE @ DATETIME = GETDATE(), @Me VARCHAR(64) = COALESCE(OBJECT_SCHEMA_NAME(@@PROCID, DB_ID()), OBJECT_SCHEMA_NAME(@@PROCID, DB_ID('tempdb')),'session')+'.'+COALESCE(OBJECT_NAME(@@PROCID, DB_ID()) , OBJECT_NAME(@@PROCID, DB_ID('tempdb')),'SQL')
SELECT ProcName = @Me

View more solutions

Comments

[Buggieboy]

It's worth noting that the returned value is of type SYSNAME.

[Vinay Sinha]

what to do for function not procedure? any idea? Please help

[Pimenta]

Still valid on SQL Serve 2012

[Fka]

Still valid on SQL Server 2016

[SAinCA]

If you use this inside a temp Proc, it returns NULL, with or without the schema name retrieval. 1st proc is "normal", 2nd is temp, in this code: BEGIN TRAN GO CREATE PROC utility.TempProc AS SELECT OBJECT_SCHEMA_NAME(@@PROCID)+'.'+OBJECT_NAME(@@PROCID) GO EXEC utility.TempProc GO ROLLBACK GO BEGIN TRAN GO CREATE PROC utility.#TempProc AS SELECT OBJECT_SCHEMA_NAME(@@PROCID)+'.'+OBJECT_NAME(@@PROCID) GO EXEC utility.#TempProc GO ROLLBACK GO

[ajeh]

Does not work for the session or global temporary stored procedures.

[Elaskanator]

@ajeh No, I get a random table name when I try this from a temporary stored procedure.

[ajeh]

@Elaskanator Provide your SQL like I did in the updated answer, we'll figure out what is going on. And your server version too, please. ATM I have access to SQL 2012 and 2016 and in both the result is NULL

[Elaskanator]

Can confirm, tested in Enterprise 2014 in 2008R2 compatibility mode with session-scoping (double ##)

[Tarek Salha]

besides the fact, that it is right: Who would create temporary procedures?? :-D

[Bill Roberts]

@Buggieboy Please elaborate: why is it worth noting the type returned is SYSNAME?