June 13, 2007 at 10:04 am
I am no tsql wonder but could someone help me out with this code (not my credit). When no parameter (a dbname) is specified it shows connections to all db's. All works, but it only shows me IF there are connections. Db's with no connections are not in results. Is it possible to show me 0 (zero) if there is no connection?
Kind rgds & thx in advance,
T.
============
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
/* Show current connections from 1 db if parameter specified or */
/* all db if no parameter specified */
/* Use 1: master.dbo.prc_showdbconnections */
/* Use 2: master.dbo.prc_showdbconnections 'master' */
CREATE PROCEDURE [dbo].[prc_Showdbconnections] @DBnaam nvarchar (255) = null
AS
BEGIN
SET NOCOUNT ON
if @DBnaam is not null
SELECT @DBnaam as 'DB_Name', dbid as 'DB_ID', hostname as 'Hostname', spid as 'SPID', program_name as 'PROGRAM_NAME', loginame as 'LOGIN_NAME', cpu as 'TOTAL_CPU', physical_io as 'TOTAL_IO', login_time as 'LOGIN_TIME'
FROM master..sysprocesses
WHERE dbid = db_id( @DBnaam ) AND spid > 50
ORDER BY cpu DESC
else
SELECT getdate(),p.dbid as 'DB_ID', d.[name] as 'DB_Name', Count(*) as 'DB_Connections', sum(cpu) as 'Total_CPU', sum(physical_io) as 'Total_IO'
FROM master..sysprocesses p
JOIN master..sysdatabases d ON d.dbid = p.dbid
WHERE spid > 50
GROUP BY p.dbid, d.[name]
ORDER BY DB_Name
END
============
June 13, 2007 at 10:16 am
Quick and dirty? Change your second query to this...
SELECT getdate(),d.dbid as 'DB_ID', d.[name] as 'DB_Name'
, Count(distinct p.spid) as 'DB_Connections', ISNULL(sum(cpu), 0) as 'Total_CPU', ISNULL(sum(physical_io), 0) as 'Total_IO'
FROM (select * from master..sysprocesses where spid > 50 ) p
RIGHT JOIN master..sysdatabases d ON d.dbid = p.dbid
--WHERE p.spid > 50
GROUP BY d.dbid, d.[name]
ORDER BY d.name
Little ugly but it'll get you your data.
June 13, 2007 at 1:18 pm
thank you very much for your help Aaron !!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply