We often need to identify the SQL text that was executed last time for a spid in SQL Server. Recently one of my friend was asking to get these information for his troubleshooting purpose. I have come up with a very simple query to help him as below.
Hope this will be helpful for those looking for similar information.
SELECT sysprc.spid,sysprc.waittime,sysprc.lastwaittype,DB_NAME(sysprc.dbid) AS database_name, sysprc.cpu,sysprc.physical_io,sysprc.login_time,sysprc.last_batch,sysprc.status, sysprc.hostname,sysprc.[program_name],sysprc.cmd,sysprc.loginame, OBJECT_NAME(sqltxt.objectid) AS [object_name],sqltxt.text FROM sys.sysprocesses sysprc OUTER APPLY sys.dm_exec_sql_text(sysprc.sql_handle) sqltxt