Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

Display DB Connections


 / Published in: SQL
 

  1. /*
  2.   SQL Server Display Database connections
  3.  
  4.   This script will show current connections to the specified database
  5. */
  6.  
  7. DECLARE @dbid int
  8. DECLARE @dbname nvarchar(50)
  9.  
  10. SET @dbname = '[Enter the database name here]'
  11. -- e.g. SET @dbname = 'mcsII'
  12.  
  13. SET @dbid = (SELECT DISTINCT(dbid) FROM master.dbo.sysprocesses WHERE db_name(dbid) = @dbname AND dbid <> 0)
  14.  
  15. SELECT spid,
  16. ecid,
  17. STATUS,
  18. loginame=rtrim(loginame),
  19. hostname,
  20. blk=convert(char(5),blocked),
  21. dbname = case
  22. when dbid = 0 then NULL
  23. when dbid <> 0 then db_name(dbid)
  24. end
  25. ,cmd
  26. ,request_id
  27. FROM master.dbo.sysprocesses
  28. WHERE dbid = @dbid

Report this snippet  

You need to login to post a comment.