BOL: Returns server-level information about the connections to SQL Server.

Connections only exist for non-internal (i.e. non-system) sessions. This dmv can be used to show ancient connections via the last_read and/or last_write datetimes. Use in conjunction with sys.dm_exec_sessions and sys.dm_exec_requests.

SELECT
   c.session_id,
   c.most_recent_session_id,
   c.connect_time,
   c.last_read,
   c.last_write,
   c.num_reads,
   c.num_writes,
   c.net_transport,
   c.encrypt_option,
   c.auth_scheme,
   c.protocol_type,
   c.protocol_version,
   c.net_packet_size,
   c.endpoint_id,
   c.client_net_address,
   c.client_tcp_port,
   c.local_net_address,
   c.local_tcp_port,
   c.node_affinity,
   c.connection_id,
   c.parent_connection_id,
   --c.most_recent_sql_handle,
   CASE WHEN st.dbid = 32767 THEN 'resourceDb' ELSE db_name(st.dbid) END as database_name,
   CASE WHEN st.dbid IS NULL THEN NULL ELSE object_schema_name(st.objectid, st.dbid) END as object_schema_name,
   CASE WHEN st.dbid IS NULL THEN NULL ELSE object_name(st.objectid, st.dbid) END as object_name,
   st.text as query_text
FROM
   sys.dm_exec_connections c
      CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) st