sql server - How to run the same query on all the databases on an instance?

Question

I have (for testing purposes) many dbs with the same schema (=same tables and columns basically) on a sql server 2008 r2 instance.

i would like a query like

SELECT COUNT(*) FROM CUSTOMERS

on all DBs on the instance. I would like to have as result 2 columns:

1 - the DB Name

2 - the value of COUNT(*)

Example:

DBName  //   COUNT (*)

TestDB1 // 4

MyDB  // 5

etc...

Note: i assume that CUSTOMERS table exists in all dbs (except master).

question from:https://stackoverflow.com/questions/18462410/how-to-run-the-same-query-on-all-the-databases-on-an-instance

Answer 1

Try this one -

SET NOCOUNT ON;

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
      [COUNT] INT
    , DB VARCHAR(50)
)

DECLARE @TableName NVARCHAR(50) 
SELECT @TableName = '[dbo].[CUSTOMERS]'

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'SELECT ''' + name + ''', COUNT(1) FROM [' + name + '].' + @TableName
    FROM sys.databases 
    WHERE OBJECT_ID('[' + name + ']' + '.' + @TableName) IS NOT NULL
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #temp (DB, [COUNT])              
EXEC sys.sp_executesql @SQL

SELECT * 
FROM #temp t

Output (for example, in AdventureWorks) -

COUNT       DB
----------- --------------------------------------------------
19972       AdventureWorks2008R2
19975       AdventureWorks2012
19472       AdventureWorks2008R2_Live