Tag Archives: SQL server – get list of active connections to each database

SQL Puzzle | How to get List of Connections, Username and Database Name on a Server

25 Friday Nov 2016

Posted by in Download SQL Interview Q's, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

Tags

, , , , ,


SQL Puzzle | How to get List of Stored Procedures along with the table names for all DBs on a SERVER

Write a script to to get List of connections, their username and the database name on a Server

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1

--

SELECT DISTINCT
    DB_NAME(dbid) databaseName , 
    COUNT(*) OVER (PARTITION BY dbid, loginame) [#Connections],
    loginame UserName
FROM sys.sysprocesses
WHERE dbid > 0 


--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com