Search This Blog

Tuesday, July 22, 2008

How to get Sql text without using dbcc inputbuffer

You can use a dynamic management view for this purpose, details given below:

Dynamic Management View:
sys.dm_exec_sql_text(sql_handle)

sql_handle SQL handle of the text to be looked up.
It retruns:
dbid - Database ID, you can get database name by using DB_Name(dbid) function.
Objectid - Object ID, you can get Object name by using Object_Name(objectid) funciton.
number - store procedures number if any
encrypted - if text is encrypted, it will have value 1
text - text of SQL query

Example:
SELECT T.TEXT, P.*
FROM SYS.SYSPROCESSES P
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) T

No comments: