view database connections in sql server

view database connections in sql server:
=======================================
To view database connections in SQL Server you can query the sysprocesses table in master database. This is a SQL Server 2000 table which is included as view in 2005 for backward compatibility. The script will show total number of connections to each database including internal connections, if you want to view only external connections change the WHERE clause to add " AND spid > 51 " to view only external connections to each database.

If you are running SQL Server 2000 use this script.

SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM master.dbo.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid

If you are using SQL Server 2005 edition then use sys.sysprocesses to get the same results.

SELECT DB_NAME(dbid) as 'Database Name',
COUNT(dbid) as 'Total Connections'
FROM sys.sysprocesses WITH (nolock)
WHERE dbid > 0
GROUP BY dbid