T-SQL Stored Procedure to Get the Number of DB Connections and Logins per Database

[sourcecode language=”text” wraplines=”false”]

/*
Script to get the number of connections and their login names on an instance

Last modified by Nick Xu on 2013-08-17

Usage:
exec sp__GetDBConnections ‘master’
*/

–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

IF (OBJECT_ID(‘sp__GetDBConnections’) IS NOT NULL)
DROP PROCEDURE sp__GetDBConnections
GO

create procedure sp__GetDBConnections
@dbname varchar(50) = N’all’ –display all DB’s last backup time by default
as
begin

–return full list if @dbanme is ‘ALL’
if upper(@dbname) = N’ALL’
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
[/sourcecode]