CODEWITHSUNDEEP
sqlsql-server-2012
Janine
Janine

Reputation: 301

How does sys.dm_exec_sql_text work?

Can you please explain why

select * from sys.dm_exec_sql_text (sql_handle) throws an error (Invalid column name 'sql_handle'), but

select * from sys.sysprocesses cross apply sys.dm_exec_sql_text (sql_handle) is a valid query? Thank you.

Upvotes: 5

Views: 21293

Answers (1)

Simon Hellings
Simon Hellings

Reputation: 176

You need to join it to another table to get the sql_handle (or plan_handle).

For example:

select a.session_id, a.start_time, status, a.command, text from sys.dm_exec_requests a cross apply sys.dm_exec_sql_text(sql_handle).

sys.dm_exec_sql_text is a table valued function, it expects the parameter sql_handle or plan_handle to be passed to it in order to return a result as other functions do. The result returned is a table (rather than a scalar function which would return a single value).

Upvotes: 6

Related Questions