I am looking for something like this, where I could store the output of sp_who2 in a table, without having to first create the table:
create table test as exec sp_who2
If you are running SQL Server 2005 SP1 and up, best is to use sp_whoisactive by Adam Machanic. It is much better than sp_who2 (see the detailed month series on the site that I linked).
Kendra (from Brent's team) has an excellent post on Collecting Data from sp_WhoIsActive in a Table
USE yourDb;
CREATE TABLE temp_sp_who2
(
SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO BIGINT NULL, -- int
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT
, RequestId INT NULL --comment out for SQL 2000 databases
)
INSERT INTO temp_sp_who2
EXEC sp_who2
SELECT *
FROM temp_sp_who2
WHERE DBName = 'yourDb'
Just tested it on SQL server 2012
Expansion of Mihai's solution for anyone that wants to capture this over time, like to track activity on a server:
IF OBJECT_ID('dbo.temp_sp_who2', 'U') IS NOT NULL
BEGIN
DROP TABLE dbo.temp_sp_who2;
END;
CREATE TABLE temp_sp_who2
(
SPID INT
,Status VARCHAR(1000) NULL
,LOGIN SYSNAME NULL
,HostName SYSNAME NULL
,BlkBy SYSNAME NULL
,DBName SYSNAME NULL
,Command VARCHAR(1000) NULL
,CPUTime INT NULL
,DiskIO INT NULL
,LastBatch VARCHAR(1000) NULL
,ProgramName VARCHAR(1000) NULL
,SPID2 INT
, RequestID INT NULL --comment out for SQL 2000 databases
, InsertedDate datetime DEFAULT GETDATE()
);
--This insert can be added to a job that runs periodically.
INSERT INTO temp_sp_who2
(
SPID
,Status
,LOGIN
,HostName
,BlkBy
,DBName
,Command
,CPUTime
,DiskIO
,LastBatch
,ProgramName
,SPID2
,RequestID
)
EXECUTE sp_who2;
SELECT *
FROM temp_sp_who2;
The question asked for a method that allows you to get the results without creating a table. This does that using a table variable:
CREATE PROC [dbo].[sp_who2db] (@DBName VARCHAR(200))
AS
BEGIN
DECLARE @who2 TABLE(
[SPID] INT,
[Status] VARCHAR(200),
[Login] VARCHAR(200),
[HostName] VARCHAR(200),
[BlkBy] VARCHAR(20),
[DBName] VARCHAR(200),
[Command] VARCHAR(200),
[CPUTime] BIGINT,
[DiskIO] BIGINT,
[LastBatch] VARCHAR(20),
[ProgramName] VARCHAR(200),
[SPID2] INT,
[RequestID] INT
)
INSERT @who2
EXEC sp_who2
SELECT * FROM @who2 WHERE DBName = @DBName
END
From Filtering sp_who2 by database by Jon Egerton.