[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]
T-SQL Stored Procedure to Get the Number of Connections and Login Names per DB
by
Tags:
Leave a Reply