Forum Discussion

Etc's avatar
Etc
Frequent Visitor
2 years ago
Solved

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?
data engineering
data warehouse
  • AndyDDC's avatar
    AndyDDC
    2 years ago

    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

5 Replies

  • AndyDDC's avatar
    AndyDDC
    Most Valuable Professional
    2 years ago

    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's avatar
      Etc
      Frequent Visitor
      2 years ago

      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's avatar
        AndyDDC
        Most Valuable Professional
        2 years ago

        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
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.