/******************************************************
 *
 * Name:         usp_who2
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     09-27-2011
 *     Purpose:  Execute a sp_who2 command and pipe 
 *               the output to a temporary table.
 * 
 ******************************************************/

/*  
	Which database to use.
*/

USE msdb
GO


/*  
	Delete the existing stored procedure.
*/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_who2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_who2]
GO
 

/*  
	Create the new stored procedure.
*/

CREATE PROCEDURE [dbo].[usp_who2]
    @VERBOSE TINYINT = 1
AS
BEGIN

    -- NO COUNTING
    SET NOCOUNT ON;
    
    -- DECLARE LOCAL VARIABLES
    DECLARE @VAR_TNAME VARCHAR(255);
    DECLARE @VAR_TSQL VARCHAR(2048);
    
    -- GET USER NAME FOR TEMP TABLE
    SET @VAR_TNAME = LOWER('[TEMPDB].[DBO].[' + ISNULL(REPLACE(SUSER_SNAME(SUSER_SID()), '\', '_'), 'DEFAULT2') + ']');
    
    -- TRUNCATE OR CREATE?
    IF  EXISTS (SELECT * FROM TEMPDB.sys.objects WHERE object_id = OBJECT_ID(@VAR_TNAME) AND type in (N'U'))
        BEGIN
            SET @VAR_TSQL = 'DELETE FROM ' +  @VAR_TNAME;
            EXECUTE (@VAR_TSQL);
        END 
    ELSE
        BEGIN
            SET @VAR_TSQL = 'CREATE TABLE ' +  @VAR_TNAME + ' (' +
                '   [spid] int not null ' + 
                ' , [status] varchar (255) not null ' + 
                ' , [login] varchar (255) not null ' + 
                ' , [host_name] varchar (255) not null ' + 
                ' , [blk_by] varchar(10) not null ' + 
                ' , [db_name] varchar (255) null ' + 
                ' , [command] varchar (255) not null ' + 
                ' , [cpu_time] int not null ' + 
                ' , [disk_io] int not null ' + 
                ' , [last_batch] varchar (255) not null ' + 
                ' , [program_name] varchar (255) null ' + 
                ' , [spid2] int not null ' + 
                ' , [request_id] int not null ' + 
                '); ';
            EXECUTE (@VAR_TSQL);
        END
        
    -- SAVE OUTPUT TO TEMP TABLE      
    SET @VAR_TSQL = 'INSERT ' +  @VAR_TNAME + ' EXEC sp_who2';
    EXECUTE (@VAR_TSQL);

    -- CHECK TEMP TABLE FOR RESULTS, MESSAGE ON IF VERBOSE
    IF (@VERBOSE = 1)
    BEGIN
        PRINT '[usp_who2] - please select from table ' + @VAR_TNAME + ' for your output.'
        PRINT ' '
    END
END
GO