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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Expanding the Synapse Forums

New forum boards available in Synapse

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Update Carousel

Fabric Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Kudoed Authors