use master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_sio'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_sio
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_sio'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_sio >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_sio >>>'
END
go
create procedure sp_sio (
        @search_str varchar(255) = null,
        @spids varchar(255) = null,
        @user varchar(255) = null
)
--with encryption
as
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    02 March 1999                                           */
/*      Description:    This stored procedure can be used to search for a       */
/*                      particular string in the last batch sent to SQL Server  */
/*                      by any user. SPIDs & username can be passed optionally. */
/*                      Hint: Use wildcards for users search if needed.         */
/*********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
set nocount on
create table #dbccio
(
SPID int null default(-1), EventType varchar(30),
Parameters int, EventInfo varchar(255) null
)
declare @str_spid varchar(11), @comma_pos int, @int_spid int
select @spids = coalesce(@spids, str(@@spid) + ','), @int_spid = 0,
        @user = '%' + @user + '%'
if right(@spids, 1) <> ',' set @spids = @spids + ','
while(1=1)
begin
 if @user is not null
 begin
   select @int_spid = min(spid)
   from master..sysprocesses
   where nt_username like @user and spid > @int_spid and spid <> @@spid
   if @int_spid is null break
   set @str_spid = str(@int_spid)
 end
 else
 begin
  set @comma_pos = charindex(',', @spids)
  if  @comma_pos = 0 break
  select @str_spid = substring(@spids, 1, @comma_pos - 1), 
         @spids = stuff(@spids, 1, @comma_pos, null)
 end
 insert #dbccio (EventType, Parameters, EventInfo)
 exec('dbcc inputbuffer(' + @str_spid + ')')
 update #dbccio set spid = @str_spid where spid = -1
end
if exists(select * from #dbccio
                where EventInfo Like '%' + coalesce(@search_str, '') + '%')
        select * from #dbccio
        where EventInfo Like '%' + coalesce(@search_str, '') + '%'
else
        print   '*********************************************************' +
                char(13) + 
                'Search string not found in any of the processes'' buffer.' +
                char(13) +
                '*********************************************************'
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_sio'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_sio To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_sio >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_sio >>>'
go

This page was last updated on May 01, 2006 04:28 PM.