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


/*
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
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in SQL Server and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s