Fixed for typos and expanded data sizes in newer versions of sql server:

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(100)

declare @emailmessage varchar(max)

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 = 'x@x.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(max),

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 = @recipients,

@subject = @emailsubject,

@message = @emailmessage

RETURN -1

end

GO