There are a few system functions for returning the number of connections but,they are many times misunderstood.
@@CONNECTIONS : Returns login attempts since the server was started.
@@MAX_CONNECTIONS: Returns maximum number of connections supported.
The simplest way to list all users and processes is to use the system stored procedure: sp_who
To list all active users: sp_who ‘active’
to list details about a particular user: sp_who ‘login-name’
Now to obtain the current number of connections to the server, you can use:
SELECT COUNT(*) AS CONNECTIONS FROM master..sysprocesses
And just to get the user connections, omitting the system processes, use:
SELECT cntr_value AS User_Connections FROM master..sysperfinfo as p
WHERE p.object_name = ‘SQLServer:General Statistics’ And p.counter_name = ‘User Connections’
To see current number of connection for each db in overall:
SELECT db_name(dbid) AS db, count(dbid) AS connection FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid