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

Who did what? Expand / Collapse
Author
Message
Posted Monday, April 28, 2008 11:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:54 AM
Points: 148, Visits: 665
Hi,

Is this script for 2005 only or does it work for 2000? Thanks.



Post #491637
Posted Monday, April 28, 2008 3:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 10:22 AM
Points: 12, Visits: 119
Looks like good info to have. . ., but my SSMS query keeps rejecting the code. It complains about the three inline queries for the elements from dm_exec_sql_text function, doesn't like the '.' within the function argument. Is this a database compatibility issue?

Any ideas?:)
Post #491756
Posted Monday, October 27, 2008 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 7:40 AM
Points: 7, Visits: 276
I like to know when who is doing in the query.

Can anybody help?

Thanks.

Ke



Post #592147
Posted Friday, December 19, 2008 12:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 8:14 AM
Points: 134, Visits: 165
Very helpful script. Thanks. I changed it a bit to make the code cleaner. This produces the same result:

USE MASTER
SELECT
s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name
,s.nt_domain, s.nt_user_name, c.client_net_address, c.local_net_address
--,c.connection_id,c.parent_connection_id, c.most_recent_sql_handle
,(SELECT text FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) as sqlscript
,(SELECT db_name(dbid) FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) as databasename
,(SELECT object_id(objectid) FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) as objectname
FROM
sys.dm_exec_sessions as s
INNER JOIN
sys.dm_exec_connections as c
ON c.session_id = s.session_id
Post #623280
Posted Friday, January 6, 2012 3:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:08 PM
Points: 321, Visits: 1,290
nice script ...amazing result which i want to see it.
thanks


Aim to inspire rather than to teach.
SQL Server DBA
Post #1231766
Posted Friday, January 6, 2012 4:15 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 17,967, Visits: 15,977
Nice script indeed.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1231777
Posted Friday, January 6, 2012 4:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 17,967, Visits: 15,977
Here's another twist on the same script

USE MASTER
Go
SELECT cr.DatabaseName
,s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name
,s.nt_domain, s.nt_user_name, c.client_net_address, c.local_net_address
,cr.ObjName
,cr.Query
FROM sys.dm_exec_sessions as s
INNER JOIN sys.dm_exec_connections as c
ON c.session_id = s.session_id
CROSS APPLY (SELECT db_name(dbid) AS DatabaseName,object_id(objectid) AS ObjName,ISNULL((SELECT text AS [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)
FOR XML PATH(''), TYPE
),'') as Query
FROM sys.dm_exec_sql_text(c.most_recent_sql_handle)) cr
ORDER BY c.session_id





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1231779
Posted Monday, January 9, 2012 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 17, 2014 2:08 PM
Points: 321, Visits: 1,290
HI SQL RNNR

I was testing your script so just want to make sure little thing in result set session_id is the SPID right?
is there possibility with this script can show up the date and time of user . like what time they ran any query?
Thanks


Aim to inspire rather than to teach.
SQL Server DBA
Post #1232429
Posted Monday, January 9, 2012 9:05 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:08 PM
Points: 17,967, Visits: 15,977
These scripts are for for current activity.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1232527
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse