Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 at 19:23
add comment

2 Answers

up vote 3 down vote accepted
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 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

SELECT  *
FROM    temp_sp_who2
WHERE   DBName = 'yourDb'

Link

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 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 at 17:33
    
@Hitesh uh, why? Do you think the output of sp_who2 is suddenly going to change? –  Aaron Bertrand Feb 23 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 at 22:04
1  
@Hitesh pain to manage a single table? As opposed to all the tables you want to create using SELECT INTO? –  Aaron Bertrand Feb 24 at 5:39
add comment

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
add comment

Your Answer

 
discard

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.