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).

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 ' + QUOTENAME(name, '''') + ', COUNT(1) FROM ' + QUOTENAME(name) + '.' + QUOTENAME(@TableName)         FROM sys.databases          WHERE OBJECT_ID(QUOTENAME(name) + '.' + QUOTENAME(@TableName)) IS NOT NULL         FOR XML PATH(''), TYPE).value('text()[1]', '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