TSQL - Averaging a result from sp_MSforeachdb

Question

I have hundreds of databases on the same SQL server (SQL2012), they all have the same basic structure and I'm trying to get the average number of 'Contacts' our databases.

I can run a sp_MSforeachdb query that presents a list of how many contacts are in the contacts table, but I need to then average out the result. Any guidance on doing this.

This is the query I've got so far:

exec sp_MSforeachdb
'use ?
IF DB_NAME() NOT IN ( "model","tempdb","master","msdb")
select count (CONTACTID) as TotalContacts from contact_tbl '

I appreciate it's likely fairly basic stuff, but I'm new here and I've googled the crap out of it and tried a lot of different suggestions from here and other places with no joy.

Any help would be appreciated.

insert the count in to a table then you can do whatever you like? — Alex K., Nov 22 '16 at 14:32

score 1 · Accepted Answer · answered Nov 22 '16 at 14:31

1

You need a temp table. Insert the Sp_msforeachdb procedure result into one temp table and find average

IF Object_id('tempdb..#avg') IS NOT NULL
  DROP TABLE #avg

CREATE TABLE #avg(cnt INT)

INSERT INTO #avg(cnt)
EXEC Sp_msforeachdb
  'use ?
    IF DB_NAME() NOT IN ( "model","tempdb","master","msdb")
    select count (CONTACTID) as TotalContacts from contact_tbl '

SELECT Avg(cnt)
FROM   #test

answered Nov 22 '16 at 14:31

Pரதீப்

91,748
19
131
172

Thank you Prdp - TSQL is still new ground, but that seems pretty basic (as suspected) - Baby steps. – KJQ Nov 22 '16 at 14:45

TSQL - Averaging a result from sp_MSforeachdb

1 Answers1