sql query to find statement currently executing in sp

  • Hi,

    After running dbcc inputbuffer() , I get only the stored procedure that is executing, but not the exact statement that is running within that stored procedure. Please provide sql query to find statement currently executing in stored procedure.

    Thanks

  • Why? What is it that you're trying to accomplish?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • you need to pass SPID or hard coded it

    use following script

    CREATE TABLE [dbo].[#Sp_info](

    [Eventtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [parameters] [int] NULL,

    [eventinfo] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY]

    GO

    declare @spid int,@lstr varchar(100),@text nvarchar(max)

    set @spid = 51

    set @lstr = 'dbcc inputbuffer( ' +cast( @spid as varchar) + ')'

    insert into #Sp_info

    exec (@lstr)

    select @text = eventinfo from #Sp_info

    SELECT sys.sysobjects.name, sys.syscomments.text

    FROM sys.sysobjects INNER JOIN syscomments

    ON sys.sysobjects.id = sys.syscomments.id

    WHERE sys.syscomments.text LIKE '%@text %' ----KEYWORD

    AND sys.sysobjects.type = 'P' ----FOR VIEW.SP/TABLE

    ORDER BY sys.sysobjects.NAME

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Input buffer only returns the name of the stored proc, if it is a stored proc that's running. If it's an ad-hoc SQL batch, it will return the query

    If you want to see the exact statement that's running within the proc, use the sys.dm_exec_requests and sys.dm_exec_sql_text DMVs, and use the statement_start_offset and statement_end_offset columns within that to substring the text.

    Something (roughly) like this:

    select

    SUBSTRING(st.text, (er.statement_start_offset/2)+1,

    ((CASE er.statement_end_offset

    WHEN -1 THEN DATALENGTH(st.text)

    ELSE er.statement_end_offset

    END - er.statement_start_offset)/2) + 1) AS statement_text

    from sys.dm_exec_requests er cross apply sys.dm_exec_sql_text(er.sql_handle) st

    where er.session_id = 54

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gila ...you are genius....i always find reply everywhere and with sounded logics:):)

    welll r u DBA or Developer and from which country?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Heh... I still want to know why the op wants to do this. :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (9/23/2008)


    Heh... I still want to know why the op wants to do this. :hehe:

    Dunno about the OP, but I had a very similar piece of code in one of my scripts to check blocking.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Yeah, but what does the OP want to do with it? 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Gila,

    i m directing you towards the problem of a lady ....please help her ,i m sure u wont mind it as i posted this reply beyond this TOPIC:)

    http://www.sqlservercentral.com/Forums/Topic573108-357-1.aspx#bm575413

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Jeff Moden (9/24/2008)


    Yeah, but what does the OP want to do with it? 🙂

    <shrug> Beats me. We'll probably never know

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply