SQL Server offers a lot of system views which allow to get information about each instance without interfering in system tables. All those system views are located in hidden system datadabase name 'Resource'. All system views can be divided into at least two groups: Structure views - which storing information about SQLSERVER properties and custom structure, and begins with schema INFORMATIN_SCHEMA.* . (compilant with ISO) Dynamic views - storing information at runtime from last time instance starts; this begins with sys.dm_* When we need to get information about executed connection in the all current opened session we can query to sys.dm_exec_connections. In this view we see two very important columns: session_id: represent the IDs of all current opened session; note that session ID is presented in each query tab in Managment Studio (ex. 52). most_recent_sql_handle: a handle to the most recent executed query for single session ID. Now assume that we want to preview...
Damian Zapart, Principal Engineering Manager @ Microsoft - official blog.