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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. 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
Solution Sage
Solution Sage

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayFBCUpdateCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.