SQL Server connection details

Get All connection details for instance.

Use this query

;with cte as (select conn.session_id,sess.[host_name],sess.[program_name],sess.client_interface_name,
sess.login_name,sess.nt_domain,sess.nt_user_name,conn.auth_scheme,conn.client_net_address,
conn.num_reads,conn.num_writes,conn.client_tcp_port,sproc.cmd,databaseid=sproc.[dbid]
from sys.dm_exec_connections conn inner join
sys.dm_exec_sessions  sess on conn.session_id=sess.session_id
inner join sys.sysprocesses  sproc on sess.session_id=sproc.spid
)
select DatabaseName=db1.name,cte.*   from cte inner join sys.sysdatabases db1 on cte.databaseid=db1.dbid
and session_id>=48 and cte.databaseid>4
order by DatabaseName

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *