CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2logging
Raj More
Raj More

Reputation: 48016

History of commands on SQL Server

This query gives me the history of commands executed on SQL Server:

Select *
From
(
SELECT      deqs.last_execution_time AS [Time],
            dest.TEXT AS [Query]
FROM        sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
) x

When I added Where x.Query LIKE '%Insert%' I get bad results (I think that this is because of the cross join):

Select *
From
(
    SELECT      deqs.last_execution_time AS [Time],
                dest.TEXT AS [Query]
    FROM        sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
) x
Where x.Query LIKE '%Insert%'

How do I get the cross join to work properly on SQL Server 2008 R2?

Upvotes: 2

Views: 8724

Answers (1)

Eystein Bye
Eystein Bye

Reputation: 5126

I am not sure what you mean by: "Bad results", but this works for me:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest 
WHERE dest.TEXT LIKE '%Insert%'

Upvotes: 3

Related Questions