Advanced Search

sp_Who Functionality Filtered by Database Name

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DECLARE @DBNm VARCHAR(50)
  2.  
  3. SET @DBNm = 'CPDB_%'
  4.  
  5. SELECT  d.name AS DBNm
  6.        ,p.hostname
  7.        ,CONVERT(sysname, rtrim(p.loginame)) AS loginname
  8.        ,p.program_name
  9.        ,  SUBSTRING(CONVERT(VARCHAR,p.last_batch,111) ,6  ,5 ) + ' '
  10.         + SUBSTRING(CONVERT(VARCHAR,p.last_batch,113) ,13 ,8 )
  11.              AS 'last_batch_char'
  12.        ,p.blocked
  13.        ,p.spid
  14.        ,p.waittime
  15.        ,p.waittype
  16.        ,ctrl.dbo.fnWaitType(p.waittype, 'Dsc') AS WaitTypeDsc
  17.        ,p.cmd
  18.        ,p.status
  19. --       ,p.sid
  20. --       ,p.cpu
  21. --       ,p.physical_io
  22. --       ,p.dbid
  23. --       ,*
  24.  FROM Master.dbo.sysProcesses p  (nolock)
  25.  LEFT JOIN Master.dbo.sysDatabases d ON d.DBId = p.DBId
  26. WHERE d.Name LIKE @DBNm
  27. ORDER BY d.name

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.