MSSQL Sessions

use msdb

DECLARE @Table TABLE(
        SPID			INT,
        Status			VARCHAR(MAX),
        LOGIN			VARCHAR(MAX),
        HostName		VARCHAR(MAX),
        BlkBy			VARCHAR(MAX),
        DBName			VARCHAR(MAX),
        Command			VARCHAR(MAX),
        CPUTime			INT,
        DiskIO			INT,
        LastBatch		VARCHAR(MAX),
        ProgramName		VARCHAR(MAX),
        SPID_1			INT,
        REQUESTID		INT)

INSERT INTO @Table EXEC sp_who2

-- Show all Sessions
	SELECT  *
	FROM    @Table
	where	DBName not in ('msdb','master','model','tempdb')
	order	by DBName desc;

-- Count Sessions by Database
	SELECT  DBName,count(*)
	FROM    @Table
	group	by DBName
	order	by DBName asc;

-- Count Sessions by Hostname
	SELECT  HostName,count(*)
	FROM    @Table
	group	by HostName
	order	by HostName asc;

-- Count Sessions by Username
	SELECT  LOGIN,count(*)
	FROM    @Table
	group	by LOGIN
	order	by LOGIN asc;

-- Show active Sessions
	SELECT  *
	FROM    @Table
	where	Status not in ('sleeping');