Execute the following script to demonstrate the listing of connection information for each session_id. The CROSS APPLY operator is used to obtain the last SQL query text for the session.

USE AdventureWorks2008;

SELECT

   ec.session_id,

   st.text as SQLText,

   ec.most_recent_session_id,

   ec.connect_time,

   ec.last_read,

   ec.last_write,

   ec.num_reads,

   ec.num_writes,

   ec.net_transport,

   ec.encrypt_option,

   ec.auth_scheme,

   ec.protocol_type,

   ec.protocol_version,

   ec.net_packet_size,

   ec.endpoint_id,

   ec.client_net_address,

   ec.client_tcp_port,

   ec.local_net_address,

   ec.local_tcp_port,

   ec.node_affinity,

   ec.parent_connection_id,

   CASE WHEN st.dbid = 32767 THEN 'Resource DB'

                  ELSE coalesce(db_name(st.dbid),'') END as DBName,

   CASE WHEN st.dbid IS NULL THEN ''

            ELSE object_schema_name(st.objectid, st.dbid) END as [Schema],

   CASE WHEN st.dbid IS NULL THEN ''

            ELSE object_name(st.objectid, st.dbid) END as [Object]

  FROM sys.dm_exec_connections ec

  CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st