Difference between revisions of "Sp capture sql--Capture sql of an active spid"

From SybaseWiki
Jump to: navigation, search
(Initial version.)
 
m
 
Line 33: Line 33:
 
</pre>
 
</pre>
 
[[Category:ASE]]
 
[[Category:ASE]]
 +
[[Category:MDA tables]]

Latest revision as of 22:57, 19 February 2009

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