Use SQL statements to implement SQL profiler

SQL profiler is a good thing. This greatly facilitates viewing the execution of SQL statements and helps us understand many internal SQL states. So there is no other way to implement it except to run SQL profiler? Of course not. Here is a simple example:

1. Use the system stored procedure sp_trace_create to create a trace. This SP can be found on-line help.

Declare @ traceidout int, @ on bit, @ RC int
-- Define the trace
Exec @ rc = sp_trace_create @ traceidout output, 2, N 'C:/sqlauditfile1' -- the last parameter is the location where the final trace result is stored.
Print @ traceidout -- print the trace ID to know which trace to stop

2. Use the system stored procedure sp_trace_setevent to notify SQL of the event to be monitored and the information returned by the event. For details about the parameters, see on-line help.

Set @ on = 1
Exec sp_trace_setevent @ traceidout, 13, 1, @ on -- the last parameter indicates that the listener is started. This parameter is of the bit type, but it cannot be directly transmitted to 1, it must be a bit variable...

-- This example listens to the SQL: batchcompleted event and returns the final SQL statement (textdata)

-- If you want to listen to multiple events or want to return multiple information, execute this statement multiple times (different parameters)

3. Use the system stored procedure sp_trace_setstatus to run the specified trace.
Exec @ rc = sp_trace_setstatus @ traceidout, @ status = 1

4. Start to execute some SQL statements.

 

5. The execution is almost complete. Use the system stored procedure sp_trace_setstatus again to stop the trace, and disable and delete the trace.

Exec sp_trace_setstatus 1, 0 -- stop trace
Exec sp_trace_setstatus 1, 2 -- disable and delete trace

(

Select * fromSYS. Traces

ID	Int	Attackers can recognize other keywords.
Status	Int	Follow these steps: 0 = stopped 1 = middle row

UseSp_trace_setstatusYou can use the following method to stop a tracing task:

Sp_trace_setstatus <identification criteria for the target tracing instance>,0

Eg:

-- Print @ traceidout
-- Select * From SYS. Traces
-- Exec sp_trace_setstatus 2, 0 -- stop trace
-- Exec sp_trace_setstatus -- disable and delete trace

Then, you can delete the generated trace file.

)

6. If the file created in step 1 is added to drive C, use SQL profilter to open it...

Note:

1. If Step 1 is not executed, the file created in step 1 cannot be opened.

2. the SQL statement does not seem to allow you to delete files by yourself. That is to say, if the files exist, you must manually delete them first.

3. The purpose of printing the trace ID in the first step is to use the trace ID in the last step because the trace ID is not executed together...

4. You can create two tasks. The first task is to execute the task early in the morning and start a trace. The second task is to execute the task at night and close the trace. The execution of SQL statements in one day will be known to the customer ..