Technical Article

Extract data from sp_who for specific database

,

sp_who (and sp_who2) work great for a quick view of what's going on in the system, and can be filtered by user, but there's no way to filter by database. This quick stored procedure provides data on which users are accessing a specific database.

CREATE PROC usp_UsersInDB
@dbname sysname
AS

/* recreate the sp_who results */CREATE TABLE #TEMP_WHO
(spid smallint,
ecid smallint,
status nchar(30) NULL,
loginame sysname NULL,
hostname nchar(128) NULL,
blk char(5),
dbname sysname NULL,
cmd nchar(16) NULL)


INSERT INTO #TEMP_WHO
EXEC sp_who

SELECT * FROM #TEMP_WHO WHERE dbname=@dbname

DROP TABLE #TEMP_WHO

Rate

Share

Share

Rate