Quick SQL connection and process details with current SQL – Version2

csmart, 2018-12-06 (first published: 2018-11-27)

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)

Related content

Automatically Creating UML Database Diagrams for SQL Server

SQL Server database developers seem reluctant to use diagrams when documenting their databases. It is probably because it has, in the past, been difficult to automatically draw precisely what you want, other than a vast Entity-relationship diagram. However, you can do it without buying any third-party tool, just using some existing Java-based open-source tools; and can even automate it entirely, using SQL and PowerShell. Phil Factor shows how.

Additional Articles

2016-05-27

5,062 reads