use [admin]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSQL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSQL]
GO


CREATE PROCEDURE [dbo].[GetSQL] 
	  @spid int
 , @display bit = 1 -- 0 for output parms only
	, @InputBuffer varchar(255) = null output
 , @SQLStmt varchar(255) = null output
AS
/*******************************************************************************************************
*	admin.dbo.GetSQL 
*	Creator: Bill Wunder      
*
*	Description:   get the input buffer and current statement for a given process
*
*	Usage:
	     EXECUTE admin.dbo.GetSQL 55
*
*
*	Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------
declare 
      @GetSQLStr nvarchar(1000)
    , @Handle varbinary(20) 

---------------------------------------------
-- create temp tables
---------------------------------------------
create table #inputbuffer
     (EventType varchar(20)
     , Parameter int
     , EventInfo varchar(255))

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------

insert #inputbuffer 
exec sp_executesql N'dbcc inputbuffer(@spid)',N'@spid int',@spid    
  
select @InputBuffer = EventInfo
from #inputbuffer

if @InputBuffer is not null
 begin

  select @GetSQLStr = ' if (select number from ::fn_get_sql(@Handle)) is null
  select @SQLStmt = ''not in cache''
 else
  select @SQLStmt = substring(substring(text,' 
                       + cast((stmt_start + 2)/2 as varchar(10)) + ' ,'
                       + cast((case when stmt_end = -1 
                                then 255 
                                else stmt_end - stmt_start 
                                end + 2)/2  as varchar(10)) + '),1,255) 
             from ::fn_get_sql(@Handle)'
    , @Handle = sql_handle
  from master.dbo.sysprocesses with(nolock) 
  where spid = @spid
  and sql_handle > 0x0

  if @GetSQLStr is null
   set @SQLStmt = 'not in cache'
  else
   exec sp_executesql @GetSQLStr, N'@Handle varbinary(20), @SQLStmt varchar(255) output', @Handle, @SQLStmt output       
 
 end -- input buffer identified

if @Display = 1
 begin
  print '' 
  select @spid [SPID]
  print '' 
  print 'InputBuffer:' 
  print @InputBuffer
  print '' 
  print 'Current executing statement:'
  print @SQLStmt 
  print '' 
 end

set @InputBuffer = replace(replace(replace(@InputBuffer,char(13),char(32)),char(10),char(32)),char(9),char(32)) 
set @SQLStmt = replace(replace(replace(@SQLStmt,char(13),char(32)),char(10),char(32)),char(9),char(32))

RETURN

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO
---------------------------------------------
-- permissions (please use a 'GO' after each permissions grant)
---------------------------------------------
--GRANT EXECUTE on [dbo].[GetSQL] to [role group or user]
GO