Create Temp Table for SP_WHO

Those days, I worked on some SQL Server issue, and i fond that there have very powerful and useful command to get spid and database relation,

that is SP_WHO;

SP_WHO

but this command is just a system stored procedures,  here is the link from MSDN for SQL Server 2008 R2;so we can not select or filter and order result of this procedure;

but we can create a temp table for result, and execute some query on the table; so first we need to create a temp table with the same columns as SP_WHO;(Here is SQL Server 2008 R2, you can check about the msdn for the different version of SQL Server, because the column maybe have some differences between different SQL Server version);

In case the table name is exist in the database; we need check about it if it's exist already;

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID(N'tempdb..#temp') AND xtype='U')
DROP TABLE [#temp]
GO

CREATE TABLE #temp
(
	spid INT,
	ecid INT,
	status VARCHAR(50),
	loginame VARCHAR(255),
	hostname VARCHAR(255),
	blk VARCHAR(255),
	dbname VARCHAR(255),
	cmd VARCHAR(255),
	request_id INT
)

and then put the record into the temp table;

INSERT INTO #temp EXEC SP_WHO

and then u can make a filter or order to this table; like select or filter and order; normally we need the spid and the some special dbname record, so...

SELECT * FROM #temp WHERE dbname ='yourdbname'