如何在实例上的所有数据库上运行相同的查询?

2022-01-16 00:00:00 sql-server multiple-databases sql-server-2008-r2

我(出于测试目的)在 sql server 2008 r2 实例上有许多具有相同架构(= 基本上相同的表和列)的数据库.

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

我想要一个类似的查询

SELECT COUNT(*) FROM CUSTOMERS

在实例上的所有数据库上.我希望有 2 列作为结果:

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

1 - 数据库名称

2 - COUNT(*)

例子:

DBName  //   COUNT (*)

TestDB1 // 4

MyDB  // 5

etc...

注意:我假设 CUSTOMERS 表存在于所有数据库中(master 除外).

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

推荐答案

试试这个 -

    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

输出(例如，在 AdventureWorks 中)-

Output (for example, in AdventureWorks) -

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

相关文章