sql 连接数查询

  SELECT * FROM master.dbo.sysprocesses WHERE dbid IN
(
SELECT dbid FROM master.dbo.sysdatabases
WHERE NAME='xxx'
) and hostprocess=11108


SELECT * FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = 'xxxx'


--连接资源进程

SELECT data2.name ,
*
FROM [master].[dbo].[sysprocesses] data1
LEFT JOIN sysdatabases data2 ON data1.dbid = data2.dbid
WHERE data1.dbid IN ( SELECT [dbid]
FROM [master].[dbo].[sysdatabases] );

 

 

SELECT data2.name ,
COUNT(1) AS ConnectNumber
FROM [master].[dbo].[sysprocesses] data1
LEFT JOIN sysdatabases data2 ON data1.dbid = data2.dbid
WHERE data1.dbid IN ( SELECT [dbid]
FROM [master].[dbo].[sysdatabases] )
GROUP BY data2.name;

 

--锁表查询

SELECT DB_NAME(resource_database_id) ,
request_session_id spid ,
OBJECT_NAME(resource_associated_entity_id, resource_database_id) tableName ,
*
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT';

SELECT *
FROM master.dbo.sysprocesses
WHERE spid = 137;

 

 

https://www.cnblogs.com/qianjinyan/p/9371263.html

 

1.xxx代表数据库名字

2.hostprocess=11108 如下图