sys.dm_exec_sql_text In SQL Server

---

• Function sys.dm_exec_sql_text can take a sql_handle or a plan_handle as a parameter,
  And it returns the SQL text that matches the handle. Any sensitive information that SQL can be included in the text, such as the password is blocked on returning SQL.
• The function in the text column contains the entire SQL batch text for the adhoc, which is prepared,
  And for self-certified queries, and items like triggers, procedures and functions, this gives full object definition. Viewing SQL text from sys.dm_exec_sql_text is useful in quickly identifying similar batches
  Due to many factors, there may be different compiled schemes, such as SET option difference.
• As an example, consider the following code, which executes two identical batches. This is example
  When we first discussed the use of DBCC FREEPROCCACHE, then it is similar to that
  A sql_handle, but this time, we see sql_handle and plan_handle values. the only difference
  Between two consecutive execution, the value of the set option is quoted _IDENTIFIER changed. It is locked in the first execution and is running in the second. After executing both batches.

---

---

we check the sys.dm_exec_query_stats view:

USE Northwind2;
GO

DBCC FREEPROCCACHE;
SET QUOTED_IDENTIFIER OFF;
GO

-- this is an example of relationship between sql_handle and plan_handle

SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO
SET QUOTED_IDENTIFIER ON;
GO

-- this is an example of the relationship between sql_handle and plan_handle

SELECT LastName, FirstName, Country
FROM Employees
WHERE Country <> 'USA';
GO

SELECT st.text, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st;
GO

 

After execution you can see two rows with the same text string and sql_handle, but with different plan_handle values.

---