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

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


Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in Database Connection, T-SQL Script 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