Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

t-sql trimming before and after stored proc name Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 1:38 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
yeah, but there's another version that supports varchar(max), which is the datatype for sys.sql_modules.definition;

it's effectively the exact same code but varchar(max) instead of varchar(8000)


good for massive strings you need to manipulate, but slower, obviously.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441897
Posted Monday, April 15, 2013 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:38 PM
Points: 8, Visits: 281
Thank you guys for all valuable feedback ...

following Query from Mansfield working gr8 in my scenario!!

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,
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
Post #1442458
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse