Current Stored Procedure

[Etc]

Hello,

I am currently trying to create a stored procedure that I previously had on a SQL DB and there are a lot of syntax differences that I have to adjust to the warehouse syntax in Fabric. One of those is getting the schema name and stored procedure name of the currently running T-SQL module in the format [schema_name].[Object_name].

In SQL DB syntax, I can get this inside the stored procedure using 

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

My current solution for Fabric is

select schema_name(schema_id) + '.' + name from sys.objects

where object_id =object_id(@@PROCID);

However, @@PROCID is not available in Fabric to get the object id of the currently running module. What could I use instead of this? Or are there any other/better ways to get the schema and object name of the currently running module?

[AndyDDC]

Hi @Etc you could try this snippet of code to get the name of the proc currently being executed

CREATE PROC dbo.spTest

AS

BEGIN

    select object_name(objectid)

    from sys.dm_exec_requests AS EC

    cross apply sys.dm_exec_sql_text(EC.sql_handle)

    where session_id=@@spid

END

[Etc]

Hi Andy,

Thank you for your answer. The code snippet you provided gives me the name of the stored procedure, but I also want the name of the schema. Is there a similar command that can get me the schema name that the SP lies in?

[AndyDDC · Accepted solution]

Something like this:

CREATE OR ALTER PROC dbo.spTest

AS

BEGIN

    select

        s.name AS SchemaName,

        object_name(objectid) AS ProcName

    from sys.dm_exec_requests AS EC

    cross apply sys.dm_exec_sql_text(EC.sql_handle)

    inner join sys.procedures pr on pr.object_id = objectid

    inner join sys.schemas s on pr.schema_id = s.schema_id

    where session_id=@@spid

END

[Etc]

Thank you, Andy, this worked!

If anyone else is trying to do the same thing, the code snippet works, however it requires the user to have the Admin role.

[v-nikhilan-msft]

Hi @Etc ,
Thanks for using Microsoft Fabric Community and reporting this.
I have reached the internal team for help regarding this. I will update once I hear back from them.
Appreciate your patience.