Sp capture sql--Capture sql of an active spid

From SybaseWiki
Jump to: navigation, search

Note: This SP uses the MDA tables.
This SP is for capturing the SQL of any active spid. If a spid is not currently processing then nothing will be returned. Usage: sp__capture_sql [spid]

create procedure sp__capture_sql
(
@spid int = null
)
as
------------------------------
-- Procedure: sp__capture_sql
--   Created: April 2008
--    Author: Bob Holmes (Email: cambob@gmail.com)
--     Usage: sp__capture_sql [spid]
------------------------------
if @spid = null
begin
   select p.spid, suser_name(p.suid), t.SQLText
   from master..monProcessStatement s, master..monProcessSQLText t, master..sysprocesses p
   where s.SPID = t.SPID
   and t.SPID = p.spid
   and p.spid <> @@spid
   and suser_name(suid) <> "sa"
end
else
begin
   select t.SQLText
   from master..monProcessStatement s, master..monProcessSQLText t, master..sysprocesses p
   where p.spid = @spid
   and s.SPID = t.SPID
   and t.SPID = p.spid
end