Finding Active Sessions in SQL Server

SELECT
    ss.session_id AS SPID, -- Session ID
    original_login_name, -- Original login name
    sp.status, -- Status
    blocking_session_id AS [Blk by], -- Blocking session ID
    command, -- Command
    db_name(sp.database_id) AS [Database], -- Database name
    [objectid], -- Object ID
    percent_complete AS [% Done], -- Percentage of completion
    CASE
        WHEN DATEDIFF(mi, start_time, GETDATE()) > 60 THEN CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) / 60)) + ' hr ' -- Duration: hours
        ELSE ''
    END +
    CASE
        WHEN DATEDIFF(ss, start_time, GETDATE()) > 60 THEN CONVERT(varchar(4), (DATEDIFF(mi, start_time, GETDATE()) % 60)) + ' min ' -- Duration: minutes
        ELSE ''
    END +
    CONVERT(varchar(4), (DATEDIFF(ss, start_time, GETDATE()) % 60)) + ' sec' AS [Duration], -- Duration: seconds
    estimated_completion_time / 60000 AS [ETA (Min)], -- Estimated time of completion in minutes
    [text] AS [input stream/text], -- Input stream or text
    SUBSTRING([text], statement_start_offset / 2 + 1,
              ((CASE
                      WHEN statement_end_offset < 0 THEN (LEN(CONVERT(nvarchar(max), [text])) * 2) -- Executing SQL statement
                      ELSE statement_end_offset
                  END) - statement_start_offset) / 2 + 1) AS [Executing_sql_statement],
    wait_resource, -- Wait resource
    wait_time / 1000 AS [wait_time (sec)], -- Wait time in seconds
    last_wait_type, -- Last wait type
    login_time, -- Login time
    last_request_start_time, -- Last request start time
    last_request_end_time, -- Last request end time
    host_name, -- Host name
    CASE
        WHEN program_name LIKE 'SQLAgent%Job%' THEN (
            SELECT TOP 1 '(SQLAgent Job - ' + name + ' - ' + RIGHT(program_name, LEN(program_name) - CHARINDEX(':', program_name)) -- Program name: SQL Agent job
            FROM msdb.dbo.sysjobs SJ
            WHERE UPPER(master.dbo.fn_varbintohexstr(SJ.job_id)) = UPPER(SUBSTRING([program_name], 30, 34))
        )
        ELSE program_name
    END AS [program_name], -- Program name
    sp.open_transaction_count, -- Open transaction count
    CASE sp.transaction_isolation_level
        WHEN 0 THEN 'Unspecified' -- Transaction isolation level: Unspecified
        WHEN 1 THEN 'ReadUncomitted' -- Transaction isolation level: Read Uncommitted
        WHEN 2 THEN 'ReadCommitted' -- Transaction isolation level: Read Committed
        WHEN 3 THEN 'Repeatable' -- Transaction isolation level: Repeatable
        WHEN 4 THEN 'Serializable' -- Transaction isolation level: Serializable
        WHEN 5 THEN 'Snapshot' -- Transaction isolation level: Snapshot
    END AS [transaction_isolation_level],
    sp.cpu_time, -- CPU time
    sp.reads, -- Reads
    sp.writes, -- Writes
    sp.logical_reads, -- Logical reads
    sp.lock_timeout, -- Lock timeout
    sp.row_count -- Row count
FROM sys.dm_exec_requests AS sp
OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS esql
RIGHT OUTER JOIN sys.dm_exec_sessions ss ON ss.session_id = sp.session_id
WHERE ss.status <> 'sleeping';