I require is to have a single stored procedure which can execute and show
"all SQL for the current connection which has previously run" and "not currently running".
I have searched a lot about this and I am not able to find the answer. Please help me on this.
It is better to use the Profiler for this task.
But if you want a query in SQL Server 2005+ you can use system views sys.dm_exec_query_stats, which returns aggregate performance statistics for cached query plans but only for the cashed plans, and sys.dm_exec_sql_text. Unfortunately they don't have information about session ID so you can get only cashed queries of a server instance.
set transaction isolation level read uncommitted
select top 1000
creation_time,
last_execution_time,
case
when sql_handle IS NULL then ' '
else(substring(st.text,(qs.statement_start_offset+2)/2,(
case
when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
end - qs.statement_start_offset)/2 ))
end as query_text,
db_name(st.dbid)as db_name,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
ORDER BY db_name, object_name
In Oracle :
How do I show running processes in Oracle DB?
In SQL server 2005+ you can find out previous ones using SPID i think.
It'll get you the process with the XXX SPID. Maybe you can get running process' SPID and create a loop until the running one's SPID. So you could get all Processes till running processes.
DECLARE @sqltxt VARBINARY(128)
SELECT @sqltxt = sql_handle
FROM sys.sysprocesses
WHERE spid = XXX
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO
Also you can get running sql statements by these;
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
it will be useful for you. I guess
