Dynamic View Control Request
I want to apply the table value function sys.dm_exec_sql_text()
for [sql_handle] in the following query.
SELECT sql_handle
FROM sys.dm_exec_query_stats A
Below 2 queries don't work for me
Select *,sys.dm_exec_sql_text(A.sql_handle)
from sys.dm_exec_query_stats A
Error:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column "sys" or the user-defined function or aggregate "sys.dm_exec_sql_text", or the name is ambiguous.
Select *
from sys.dm_exec_query_stats A,
sys.dm_exec_sql_text(A.sql_handle)
Error:
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "A.sql_handle" could not be bound.
+3
source to share
2 answers
You can CROSS APPLY :
...
from sys.dm_exec_query_stats A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle)
as an example from Microsoft:
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
+3
source to share
If you want to do this for every sql_handle this will create your assertions.
declare @varbin as varchar(max)
declare @sql2 as varchar(200)
DECLARE db_cursor CURSOR
FOR select convert(varchar(max),sql_handle,2) from sys.dm_exec_query_stats
OPEN db_cursor
FETCH NEXT FROM db_cursor
into @varbin
WHILE @@FETCH_STATUS = 0
BEGIN
set @varbin = '0x'+@varbin
set @sql2 = 'sys.dm_exec_sql_text('+@varbin+')'
print @sql2
FETCH NEXT FROM db_cursor
INTO @varbin
END
CLOSE db_cursor
DEALLOCATE db_cursor
+2
source to share