SQL Server query loggin

Question

When i execute a drop view query i don't see it in the query logging.
Goal is to store all the executed queries. How can i achieve this?

use [MY_DB] 
go
create view test as select '1' as one
 ; go
 DROP VIEW [dbo].[test] ; 
SELECT sql_text.text, st.last_execution_time, DB_NAME(qp.dbid) as databasename FROM sys.dm_exec_query_stats st CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS sql_text INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = st.plan_handle CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) as qp WHERE st.last_execution_time >= DATEADD(week, -1, getdate()) ORDER BY last_execution_time DESC; 

Answer 1

Goal is to store all the executed queries. How can i achieve this?

You'll need to create your own auditing via any of the many tools that exist for this: Trace (deprecated), XE, Audits (also XE under the covers). In your example, there is DDL and since there is only a single way to execute DDL it doesn't make sense to have and cache a plan for it.

Answer 2

Hi @Anonymous ,

When you need to review the data about executed SQL statements in SSMS for a particular period, you can use several options:

1.Queries are saved in the cache via system representations (sys.dm_exec_query_stats, sys.dm_exec_sql_text, and sys.dm_exec_query_plan). But this script will only give you recent queries from the cached plan. There is no guarantee that it will be accurate. It is just an indication and helps to do a basic diagnosis of the system. If you remove the plan from the cache, you will not see that row as a result of your query.
2.Using SQL Server Profiler
3.Using Extended Events
4.Using the Query Store, starting from the 2016 version
5.Using SQL Complete (SQL Complete\Execution History) in SSMS

Please refer to the blog How to Check SQL Server Query History to get more information.

I found a similar thread, hope it could help you.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.