SQL Server Portal

sys.sysprocesses is one of the helpful system views when it comes to query the processes of SQL Server. I have been using this system view for quite a while. But a few days ago I read on MSDN, “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 uses this feature.”

So, I planned to upgrade my query.
Given below is my query that I used to retrieve processes using specified database since SQL Server 2000.

--This query will work fine on SQL Server 2000 and above.
Declare @database_name as varchar(max)
Set @database_name ='AdventureWorks2012'

Select [spid] as [session_id], [last_batch],[status]
,cmd as [Command], hostname as [host_name]
,[program_name], [loginame] as [Login_name]
from sys.sysprocesses
Where dbid = db_id(@database_name)

Given below is my new query that works on SQL Server 2012 and above, having the same result set as the above.

Declare @database_name as varchar(max)
Set @database_name ='AdventureWorks2012'

Select A.session_id,A.last_request_end_time as [last_batch],A.[status]
, B.[command],A.[host_name]
,A.[program_name],A.[login_name]
from sys.dm_exec_sessions A
Left Join sys.dm_exec_requests B
On A.[session_id]=B.[session_id]
Where A.[database_id]=db_id(@database_name)

Conclusion :
You can see that both the above queries result set is same but first query (sys.sysprocesses) will not be applicable for future versions of SQL.

Let me know your feedback regarding both queries.