This SQL script from Richard Ding checks the current database activity in SQL server by using a cursor to loop through the db name list into which table joins pull out rows from sysprocesses and syslockinfo. The result of table names currently being used as well as the type of locks set on them are then displayed. The script differs with sp_who or sp_who2 in that it shows what object and lock the spid is accessing. It is useful in monitoring processes such as DBCC DBREINDEX. Running this script constantly will disclose a dynamic pattern of a certain process to better understand what it is doing.
It has been tested on SQL 7 and 2000.
