Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
What version of SQL Server are you on? – Aaron Bertrand Feb 23 '14 at 19:23
up vote 4 down vote accepted

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

share|improve this answer
USE yourDb;

CREATE TABLE temp_sp_who2
      SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName 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


INSERT  INTO temp_sp_who2
EXEC sp_who2

FROM    temp_sp_who2
WHERE   DBName = 'yourDb'


Just tested it on SQL server 2012

share|improve this answer
Thanks a lot Mihai, I was wondering if there was anything that could copy the table structure from sp_who2 so that we don't have to create one by ourselves. – Hitesh Chouhan Feb 23 '14 at 17:27
I doubt it,because sp_who2 is not a table,so you cant do CREATE AS SELECT,but maybe someone knows more. – Mihai Feb 23 '14 at 17:33
@Hitesh uh, why? Do you think the output of sp_who2 is suddenly going to change? – Aaron Bertrand Feb 23 '14 at 19:24
@mihai thanks for your response, i was just thinking that i don't have to take up the pain to manage the table. – Hitesh Chouhan Feb 23 '14 at 22:04
@Hitesh pain to manage a single table? As opposed to all the tables you want to create using SELECT INTO? – Aaron Bertrand Feb 24 '14 at 5:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.