T-SQL Stored Procedure to Get the Number of Connections and Login Names per DB

[code language="sql"]
/*
Script to get the number of connections and their login names on an instance

Last modified by Nick Xu on 2014-03-05

Usage:
EXEC sp__GetDBConnections 'master'
EXEC sp__GetDBConnections
*/

--For generic setting
USE master
GO

IF EXISTS(SELECT * FROM sysobjects WHERE name = 'sp__GetDBConnections' AND type = 'P')
BEGIN
	DROP PROC sp__GetDBConnections
END
GO

CREATE PROCEDURE sp__GetDBConnections
@dbname VARCHAR(100) = NULL --display all DB's connections IF db name is not specified
AS
BEGIN

	--return full list IF @dbanme is NULL
	IF @dbname is NULL
	BEGIN
		SELECT
			DB_NAME(dbid) AS DBName,
			COUNT(dbid) AS [#Connections],
			loginame AS Login_Name
		FROM
			sys.sysprocesses
		WHERE
			dbid > 0
		GROUP BY
			dbid, loginame
		ORDER BY [#Connections] desc
	END
	ELSE
	BEGIN
		SELECT
			DB_NAME(dbid) as [DBName],
			COUNT(dbid) as [#Connections],
			loginame as Login_Name
		FROM
			sys.sysprocesses
		WHERE
			DB_NAME(dbid) = @dbname
		GROUP BY
			dbid, loginame
		ORDER BY [#Connections] desc
	END
END
GO

[/code]