SQL Server

Some Simple Queries That Might Be Helpful

Here are some simple queries that might be helpful for you.

To check number of open connection in SQL server: 

  1. SELECT DB_NAME(dbid) AS DBName, COUNT(dbid) AS NumberOfConnections, loginame AS LoginName  
  2. FROM sys.sysprocesses  
  3. WHERE dbid > 0  
  4. GROUP BY dbid, loginame;  

To check the number of queries running currently on your server:

  1. SELECT sqltext.TEXT, req.session_id, req.STATUS, req.command, req.cpu_time, req.total_elapsed_time  
  2. FROM sys.dm_exec_requests REQ  
  3. CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;  

Find all dependencies of a table in Sql server 

  1. SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc  
  2. FROM sys.dm_sql_referencing_entities('dbo.Clients', 'OBJECT');  

Shows all the sessions that are currently established in Sql server. 

  1. EXEC sp_who2  
  2.   
  3. //Or  
  4.   
  5. EXEC sp_who  

Shows all ACTIVE sessions that are currently established in SQL server. 

  1. EXEC sp_who 'Active'  
  2.   
  3. //Or  
  4.   
  5. EXEC sp_who2 'Active'  
Membership not found