Capture SQL Server queries without third-party tooling and without using deprecated features?

You could use Extended Events to capture this data. However, depending on how much traffic your server gets this could become a lot of data very quickly and could result in performance issues.

I would look at limiting the events that you track to something like sqlserver.rpc_completed or sqlserver.sql_statement_completed, these only capture what was completed. Erin Stellato wrote a great piece on useing XEvent Profiler to capture queries in SQL Server.

From SSMS, you would go to Management > Extended Events > Session and either use the New Session Wizard or New Session to start building your session to track data. The Microsoft Docs go into a lot of detail on how to set this up.

Here is an example of something I implemented recently to capture queries on a specific server, you can add filters to remove queries that you don't want to see - like some server names or application names that execute queries as well:

CREATE EVENT SESSION [Track Queries] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%name%') 
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name') 
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%') 
            AND [sqlserver].[server_principal_name]<>N'<username>')),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.sql_text,sqlserver.username)
    WHERE (NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_hostname],N'%<name>%') 
            AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'name') 
            AND NOT [sqlserver].[like_i_sql_unicode_string]([sqlserver].[client_app_name],N'%name%') 
            AND [sqlserver].[server_principal_name]<>N'<username>'))
ADD TARGET package0.event_file(SET filename=N'D:\XE\TrackQueries.xel',max_file_size=(5120))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

This captures the details of what has been executed and puts it into a file for easy querying and analysis.


Use SQL Server Extended Events to capture T-SQL statements as they execute.

SQL Server Management Studio 17+ includes an "XEvent Profiler" item in the Object Explorer for every connected SQL Server that is Version 2012 or higher. Right-Click the TSQL session, then Launch Session.

Be aware, capturing T-SQL statements across an entire server can negatively affect performance, so you'd likely only want to do that for a short period of time before stopping the session.