kalpit_yellow (4/10/2013)
Thanks for the reply...actually i have following query and i want actual stored proc name instead of whole procudere on Last_executed_SQL column...SELECT ec.session_id, connect_time,s2.[text] as Last_Executed_SQL
, auth_scheme as Auth_Type, client_net_address
,client_tcp_port, es.host_name, es.program_name, es.login_time, es.login_name
FROM sys.dm_exec_connections ec
OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2
LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)
WHERE s2.text LIKE ('%Stored procedure%')
ORDER BY connect_time desc
You can join back to sys.objects via sys.sql_modules to get the stored procedure name. This will make the query only show running stored procedures. e.g.
SELECTec.session_id,
connect_time,
s2.[text] as Last_Executed_SQL,
auth_scheme as Auth_Type,
client_net_address,
client_tcp_port,
es.host_name,
es.program_name,
es.login_time,
es.login_name,
ob.name as Stored_Procedure_Name
FROM sys.dm_exec_connections ec
OUTER APPLY sys.dm_exec_sql_text (ec.most_recent_sql_handle) as s2
LEFT JOIN sys.dm_exec_sessions es on (ec.session_id = es.session_id)
JOIN sys.sql_modules sm on sm.definition = s2.text
JOIN sys.objects ob on ob.object_id = sm.object_id
ORDER BY connect_time desc