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

      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.