Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Mircea Nita,
Assuming you have a large number of databases with hundreds of active users on a server, it is often very difficult to follow a blocking chain that affects one of the databases, since sp_who2 returns all the processes for all the databases available.
This is the reason why I have modified sp_who2 and the new stored procedure called sp_who3 limits its scope only to the database name passed as parameter, or databases that match the substring in the parameter.
Without a parameter sp_who3 returns a listing similar to the output of sp_who2 ordered by SPID. (exec sp_who3)
Another way of using sp_who3 is passing a database name as parameter. The stored procedure will return only those user processes relating to the specified database. It is therefore easy to follow a blocking chain for example. (exec sp_who3 'DatabaseName').
You can also use sp_who3 by passing a part of the database name as parameter.
This is particularly useful if for example you have a large number of Test databases and a large number of Beta databases on the same server and you have a consistent naming convention, like <DatabaseName>Test and <DatabaseName>Beta.
You can use sp_who3 to display user processes relating to a database subset of all databases available on the server like this: exec sp_who3 'Beta'
This is what I use to rapidly find out which users are logged on to the Beta system. This allows me to see at a glance whether the logged users are just backend services, or if real operators use the Beta system for testing.
I hope that you will find sp_who3 useful and easy to use.
A little tweak to the original sp_who2 with much more useful info
What are your options for sending a variable number of choices in a parameter to a stored procedure?...
Comments posted to this topic are about the item [b][url=/scripts/sp_who3/69906/]DBA Tools: sp_who3[...
DATABASE NAME AS PARAMETER IN PROCEDURE
Get processes run and the command.