Quick SQL connection and process details with current SQL - Version2

,

Step 1: Create the view

Step 2: Join with sys.sysdatabases and sys.dm_exec_connections check the connection details

CREATE view [dbo].[sqlexec] as
with SQLExec as
(SELECT
s.spid,db_name(s.dbid) as dbname,s.login_time,s.last_batch,s.hostname, s.loginame, s.program_name,s.waittype,s.lastwaittype,s.cmd,s.blocked,s.cpu,s.memusage,s.physical_io,s.status,s.net_library,s.sql_handle
,e.text as SQL,e.encrypted as IsSQLTextEncrypted
FROM 
sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS e)
select e.*,conn.auth_scheme,conn.client_net_address,conn.connect_time,conn.encrypt_option as IsSQLConnectionEncrypted,conn.net_transport from SQLExec e
join sys.dm_exec_connections conn with (nolock) on e.spid = conn.session_id

Rate

3.5 (4)

Share

Share

Rate

3.5 (4)