In SQL Server, you can retrieve information about all active database sessions using Transact-SQL (T-SQL) by querying the sys.dm_exec_sessions dynamic management view. This view provides detailed information about each session connected to the SQL Server instance, including information about the database they are connected to. Here's how you can get all database sessions using T-SQL:

-- Query to get all active database sessions
SELECT
s.session_id AS SessionID,
s.login_name AS LoginName,
s.status AS SessionStatus,
DB_NAME(s.database_id) AS DatabaseName,
s.host_name AS HostName,
s.program_name AS ProgramName
FROM sys.dm_exec_sessions AS s
WHERE s.database_id > 0 -- This filters out system sessions
-- Additional columns can be included based on your requirements

Let's break down the query:

We select several columns from the sys.dm_exec_sessions dynamic management view to gather information about each session.
We use the session_id column to uniquely identify each session.
The login_name column displays the login name of the user or application connected to the session.
The status column indicates the current status of the session (e.g., running, sleeping).
We use the DB_NAME(s.database_id) function to get the name of the database to which the session is connected. Sessions with a database_id of 0 are typically system sessions.
The host_name column shows the name of the computer from which the session is connected.
The program_name column displays the name of the application or program that initiated the session.

You can customize this query based on your specific requirements. For example, you may want to filter sessions based on a particular database, application name, or user login. You can do so by adding additional conditions in the WHERE clause. Additionally, you can include more columns from the sys.dm_exec_sessions view to gather additional information about the sessions as needed.

See also: SP_WHO