See list of all SQL Queries run on server

Question

Is there a query I can run which will display all the queries which have been run on the server within a date range for a specific database?

I need to find out what parameter values were passed to a Stored Procedure which was executed last week

Solution 1

No

The only way this can be done is if a monitoring process is set up in advance.

Monitoring a database, e.g. through SQL Profiler, has a performance impact, so one should be wary about using this against a live system for prolonged periods.

A better way to monitor this kind of activity, would be by use of logging from the calling code.

Solution 2

try the below:

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
ORDER BY deqs.last_execution_time DESC

http://blog.sqlauthority.com/2008/01/03/sql-server-2005-last-ran-query-recently-ran-query/