Script to output dbcc inputbuffer adding spid info

use master
go


alter procedure sp_inputbuffer
as

----------------------------------------------------------
----
-- THIS PROCEDURE WAS CREATED TO GET THE LATEST INPUT --
-- FROM THE VARIOUS PROCESSES RUNNING ON THE SERVER.--
-- IT COMBINES DBCC INPUT BUFFER WITH 
-- CREATED BY SCOTT DEXTER--
--     DATABASE CONSULTANT--
-- CREATED ON 2006-09-29--
-- ALL RIGHTS RESERVED BY SCOTT M. DEXTER ALL USERS     --
-- MUST SIGHT ME IN THE CREDITS FOR THIS PROCEDURE.     --
----------------------------------------------------------


declare @currentuser sysname
declare @currentprocedure sysname
declare @emailsubject varchar(30)
declare @emailmessage varchar(300)
declare @time varchar(20)
declare @recipients varchar(100)

select @time = convert(varchar(20), getdate(), 120)
select @currentuser = rtrim(suser_sname())
select @currentprocedure = object_name(@@procid)
set @recipients = 'sdexter@carrols.com'


------------------------------------------------------------------
-- CHECK THE PERMISSIONS OF THE PERSON EXECUTING THIS PROC.-- 
-- ONLY AUTHORIZED PERSONNEL WILL BE ALLOWED TO EXECUTE THIS--
-- PROCEDURE.--
------------------------------------------------------------------
if (not is_member('db_securityadmin') = 1) and
       (not is_member('db_owner') = 1)
begin
set @emailsubject = 'Security Violation.'
set @emailmessage = 'The following user: ""' + @currentuser + '""' + char(10) + char(13) + 
'does not have permission to run the following procedure: ""' + @currentprocedure + '""' + char(10) + char(13) + 
'at the following date and time.' + @time
goto hellodolly
end

-- CREATES TEMP TABLE TO INSERT DATA INTO. THE 
-- TABLE CAN BE ADDED TO FOR ADDITIONAL INFORMATION.
create table #inputbuffer
(
eventType varchar(255) ,
parameters int ,
procedureText varchar(255),
spid varchar(6)
)


declare @spid varchar(6)
declare @sql varchar(50)

-- CREATE CURSOR TO GET INFORMATION FROM DBCC INPUTBUFFER AND 
-- ADDITIONAL COLUMNS INTO THE TEMP TABLE FOR OUTPUT TO SYSADMINS
-- AND THOSE SO FORTUNATE TO BE GRANTED THE AUTHORITY OF DBO.
declare sprocket cursor fast_forward for 
select spid from master.dbo.sysprocesses
where program_name is null or program_name <> ''

open sprocket
fetch next from sprocket into
@spid

while @@fetch_status = 0
begin
set @sql = 'dbcc inputbuffer(' + @spid + ')'
insert into #inputbuffer(eventType, parameters, procedureText)
exec (@sql)

update #inputbuffer
set spid = @spid
where spid is null
-- ADDITIONAL UPDATES CAN BE PLACED HERE IF YOU SO DESIRE MORE INFORMATION.
-- PERSONALLY I THINK THAT THIS GIVES ME A GOOD PLACE TO START TO FIND ISSUES
-- THAT ARE GOING ON WHICH I CAN USE TO TROUBLESHOOT PERFORMANCE PROBLEMS OR 
-- OTHER.

fetch next from sprocket into
@spid
end

close sprocket
deallocate sprocket

-- CHECK TO SEE IF THE CURSOR IS STILL OPEN.
if @@cursor_rows <> 0
begin 
close sprocket
deallocate sprocket
end


-- OUTPUT THE INFORAMTION TO THE SCREEN FOR ANALYSIS.
select spid, eventType, parameters, procedureText
from #inputbuffer 



-- IF THE TABLE STILL EXISTS (WHICH IT SHOULD) THEN DROP IT.
if (object_id('tempdb..#tb1_sysprocesses') is not null)
            drop table #tb1_sysprocesses
return(0)

hellodolly:
begin
exec master.dbo.xp_sendmail @recipients = @emailrecipients,
@subject = @emailsubject,
@message = @emailmessage

RETURN -1
end

GO