Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

Reply
Etc
Frequent Visitor

Current Stored Procedure

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?
1 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

View solution in original post

5 REPLIES 5
AndyDDC
Super User
Super User

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
Frequent Visitor

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?

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
Frequent Visitor

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
Community Support
Community Support

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

JanFabricDE_carousel

Fabric Monthly Update - January 2025

Explore the power of Python Notebooks in Fabric!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.