use master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_tlist'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_tlist
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_tlist'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_tlist >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_tlist >>>'
END
go
create procedure sp_tlist (
        @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 list the input     */
/*                      buffer for the last batch sent to SQL Server by any user*/
/*                      SPIDs & username can be specified optionally. Hint: Use */
/*                      wildcards for users search if needed.                   */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
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
   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
 print 'SPID = ' + ltrim(@str_spid)
 print '---------------------------'
 exec('dbcc inputbuffer(' + @str_spid + ')')
end
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_tlist'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_tlist To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_tlist >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_tlist >>>'
go

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