sp_who by database sp_who_db

CREATE PROCEDURE sp_who_db  --- 2002/07/26  
    @dbname     sysname = NULL  
as  
  
set nocount on  
  
declare  
    @retcode         int  
  
declare  
    @sidlow         varbinary(85)  
   ,@sidhigh        varbinary(85)  
   ,@dbid1           varbinary(85)  
   ,@spidlow         int  
   ,@spidhigh        int  
  
declare  
    @charMaxLenLoginName      varchar(6)  
   ,@charMaxLenDBName         varchar(6)  
   ,@charMaxLenCPUTime        varchar(10)  
   ,@charMaxLenDiskIO         varchar(10)  
   ,@charMaxLenHostName       varchar(10)  
   ,@charMaxLenProgramName    varchar(10)  
   ,@charMaxLenLastBatch      varchar(10)  
   ,@charMaxLenCommand        varchar(10)  
  
declare  
    @charsidlow              varchar(85)  
   ,@charsidhigh             varchar(85)  
   ,@charspidlow              varchar(11)  
   ,@charspidhigh             varchar(11)  
  
--------  
  
select     @retcode         = 0      -- 0=good ,1=bad.  
   
--------------------------------------------------------------  
IF (@dbname IS     NULL)  --Simple default to all DatabaseNames.  
      GOTO LABEL_17PARM1EDITED  
  
--------  
  
select @dbid1 = null  

if exists(select * from master.dbo.sysdatabases where name = @dbname)  
 select @dbid1 = dbid from master.dbo.sysdatabases where name = @dbname  
  
IF (@dbid1 IS NOT NULL)  --Parm is a recognized db name.  
   begin  
   GOTO LABEL_17PARM1EDITED  
   end  
--------  
  
RaisError(15010,-1,-1,@dbname)  
select @retcode = 1  
GOTO LABEL_86RETURN  
  
  
LABEL_17PARM1EDITED:  
 
--------------------  Capture consistent sysprocesses.  -------------------  

SELECT  
  spid  
 ,status  
 ,sid  
 ,hostname  
 ,program_name  
 ,cmd  
 ,cpu  
 ,physical_io  
 ,blocked  
 ,dbid  
 ,convert(sysname, rtrim(loginame))  
        as loginname  
 ,spid as 'spid_sort'  
 ,  substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '  
  + substring( convert(varchar,last_batch,113) ,13 ,8 )  
       as 'last_batch_char'    
      INTO    #tb1_sysprocesses  
      from master.dbo.sysprocesses   (nolock)  
      
if @dbid1 is not null 
     delete #tb1_sysprocesses where dbid != @dbid1


  
--------Prepare to dynamically optimize column widths.  
  
  
Select  
    @charsidlow     = convert(varchar(85),@sidlow)  
   ,@charsidhigh    = convert(varchar(85),@sidhigh)  
   ,@charspidlow     = convert(varchar,@spidlow)  
   ,@charspidhigh    = convert(varchar,@spidhigh)  
  
  

SELECT  
             @charMaxLenLoginName =  
                  convert( varchar  
                          ,isnull( max( datalength(loginname)) ,5)  
                         )  
  
            ,@charMaxLenDBName    =  
                  convert( varchar  
                          ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)  
                         )  
  
            ,@charMaxLenCPUTime   =  
                  convert( varchar  
                          ,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)  
                         )  
  
            ,@charMaxLenDiskIO    =  
                  convert( varchar  
                          ,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)  
                         )  
  
            ,@charMaxLenCommand  =  
                  convert( varchar  
                          ,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)  
                         )  
  
            ,@charMaxLenHostName  =  
                  convert( varchar  
                          ,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)  
                         )  
  
            ,@charMaxLenProgramName =  
                  convert( varchar  
                          ,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)  
                         )  
  
            ,@charMaxLenLastBatch =  
                  convert( varchar  
                          ,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9)  
                         )  
      from  
             #tb1_sysprocesses  

  
--------Output the report.  
  
  
EXECUTE(  
'  
SET nocount off  
  
SELECT  
             SPID          = convert(char(5),spid)  
  
            ,Status        =  
                  CASE lower(status)  
                     When ''sleeping'' Then lower(status)  
                     Else                   upper(status)  
                  END  
  
            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')  
  
            ,HostName      =  
                  CASE hostname  
                     When Null  Then ''  .''  
                     When '' '' Then ''  .''  
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')  
                  END  
  
            ,BlkBy         =  
                  CASE               isnull(convert(char(5),blocked),''0'')  
                     When ''0'' Then ''  .''  
                     Else            isnull(convert(char(5),blocked),''0'')  
                  END  
  
            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')  
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')  
  
            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')  
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')  
  
            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')  
  
            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')  
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.  
      from  
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().  

  
      -- (Seems always auto sorted.)   order by spid_sort  
  
SET nocount on  
'  
)  
  
LABEL_86RETURN:  
  
  
if (object_id('tempdb..#tb1_sysprocesses') is not null)  
            drop table #tb1_sysprocesses  
  
return @retcode -- sp_who_db