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:
IF DB_NAME() NOT IN ( "model","tempdb","master","msdb")
select count (CONTACTID) as TotalContacts from contact_tbl '
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