To view the SQL that is being executed

Brief introduction
It  is known that the SP_WHO2 command is used to view the SPIDs of the currently executing SQL statement. But the display
Information is limited. For example, it only shows execution select,delete,update, and so on. The actual SQL command is not visible.

Know which SQL statement is being executed for our debug program, find out which statements take a long time, or which statements produce
Clogging is very important. And this is often used to see which step the stored procedure executes. To which statement to execute.

So we need a better command than Sp_who2 to do the work.
This command uses the dynamic management view of SQL Management Views (DMVs). So just do it in SQL2005 or
On a higher version.

Return To field Introduction:

Column Name	Type	Describe
spid	smallint	SQL Server process ID.
Ecid	smallint	Econtext ID
dbid	smallint	Database ID
Nt_username	NCHAR (128)	User name
Status	NCHAR (30)	State
Wait_type	bigint	The current waiting milliseconds.
Individual Query	varchar	The currently executing SQL statement
Parent Query	varchar	Upper-level SQL statements
Program_name	NCHAR (128)	Program Name
Hostname	NCHAR (128)	Host Name
Nt_domain	NCHAR (128)	Domain name
Start_time	Datetime	Start time

CREATE PROC [dbo]. [Dba_whatsqlisexecuting] As /*-------------------------------------------------------------------- Displays the currently executing SQL statement ---------------------------------------------------------------------- How to use: 1. Exec YourServerName.master.dbo.dba_WhatSQLIsExecuting ---------------------------------------------------------------------*/ BEGIN --Does not lock anything, and does not get held to any locks. SET TRANSACTION Isolation Level READ UNCOMMITTED -What SQL statements is currently Running? SELECT [Spid] = session_id , ecid , [Database] = db_name (sp.dbid) , [User] = Nt_username , [Status] = Er.status , [Wait] = Wait_type , [individual Query] = SUBSTRING (Qt.text, ER.STATEMENT_START_OFFSET/2, (case when er.statement_end_offset =-1 Then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2 ELSE Er.statement_end_offset End- Er.statement_start_offset)/2) , [Parent Query] = Qt.text , program = Program_name , Hostname , Nt_domain , start_time From sys.dm_exec_requests ER INNER JOIN sys.sysprocesses sp on er.session_id = Sp.spid Cross APPLY Sys.dm_exec_sql_text (er.sql_handle) as Qt WHERE session_id >-Ignore system SPIDs. and session_id not in (@ @SPID)--Ignore the current statement. ORDER by 1, 2 END

To view the SQL that is being executed