This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

And we follow the link for Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views we see that sysprocesses is mapped to sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests. As it happens these are three of my favorite DMOs. Database_Id is in sys.dm_exec_requests but that only helps if the session is actually doing something. In SQL 2012 and up you can find database_id in sys.dm_exec_sessions and this appears to map exactly to the dbid in sysprocesses. So quest solved right! Nope, 90% of the servers I’m working in currently are pre 2012. So now I’m in kind of a conundrum. I could write a query using sysprocesses, but I try to avoid using deprecated features when I can, or I can use sp_who, which I already complained about above.

Then I remembered sys.dm_tran_locks. This DMO displays information on locks currently held in the system. And yay us, being in a database is considered a lock! In fact it is a shared database lock. So now we write the query:

SELECT sys.databases.name DB_Name, request_session_id
FROM sys.dm_tran_locks
JOIN sys.databases
	ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type = 'DATABASE'
ORDER BY request_session_id

This is great! Except that it doesn’t show a shared database lock for master. Not sure why, but it doesn’t. Not really a big deal since chances are I’m never going to be doing anything to master that requires me to know who’s in it. I could go and look in sys.dm_exec_sessions for any session_ids that aren’t in sys.dm_tran_locks but then I’m going to get a number of sessions that, at least according to sysprocesses, belong to dbid 0. It probably doesn’t matter since they are all going to be system connections and again I don’t really need that information anyway. But just in case here is the code anyway.

SELECT sys.databases.name DB_Name, request_session_id
FROM sys.dm_tran_locks
JOIN sys.databases
	ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type = 'DATABASE'
UNION ALL
SELECT 'master' DB_Name, session_id
FROM sys.dm_exec_sessions
WHERE sys.dm_exec_sessions.session_id NOT IN (
		SELECT request_session_id
		FROM sys.dm_tran_locks
		WHERE resource_type = 'DATABASE')
ORDER BY request_session_id