Obtener información de las conexiones existentes.

Si desea obtener información de las conexiones existentes a una base de datos, por el nombre del host, por el nombre de la base de datos o el inicio de sesión.


Drop table #TMP_TABLE

CREATE TABLE #TMP_TABLE (
SPID INT,
ecid int,
STATUS VARCHAR(32),
LOGINAME VARCHAR(32),
HOSTNAME VARCHAR(32),
BLK CHAR(8),
DBNAME VARCHAR(32),
CMD VARCHAR(255),
request_id int )

INSERT INTO #TMP_TABLE EXEC sp_who

SELECT COUNT(*)
FROM #TMP_TABLE
WHERE DBNAME = (nombre de la base de datos)
AND LEN(LTRIM(RTRIM(HOSTNAME))) > 0
AND HOSTNAME <> (maquina)


SELECT *
FROM #TMP_TABLE


-- Por base de datos
SELECT dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by dbname
order by conexiones desc

SELECT COUNT(*) as total
FROM #TMP_TABLE
where dbname is not null

/*

SELECT dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null and status <> 'background'
group by dbname
order by conexiones desc


SELECT COUNT(*) as total
FROM #TMP_TABLE
where dbname is not null and status <> 'background'
*/


-- por usuarios

SELECT hostname, loginame, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, loginame, dbname
order by hostname, loginame, dbname, conexiones


SELECT hostname, loginame, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, loginame, dbname
order by conexiones desc, hostname, loginame, dbname



-- por maquina y base

SELECT hostname, dbname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname, dbname
order by conexiones desc, hostname, dbname


-- por maquina

SELECT hostname, COUNT(*) as conexiones
FROM #TMP_TABLE
where dbname is not null
group by hostname
order by conexiones desc, hostname


-- activas
SELECT *
FROM #TMP_TABLE
where dbname is not null
and status='runnable'


SELECT *
FROM #TMP_TABLE
where dbname is not null
and hostname = 'una aplicación'