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