query sql server in multiple database-CodePudding

i would like to execute this query 'select count(*) from Aircraft' on multiple database. We have 50 database and all of those have the same table. i'm using sql server 2019. I know there is a possibilty to loop this query so that's why i'm asking you.

I found some old reply but not recently.

I used this query but it didn't work


SELECT @Query = COALESCE(@Query   ' UNION ALL ', '')   'select * from ['   TABLE_CATALOG '].dbo.[Aircraft]'
FROM information_schema.tables 

SET @Query = STUFF(@Query, CHARINDEX('UNION ALL', @Query), 10, '')

PRINT @Query

EXEC(@Query)

CodePudding user response：

You can achieve this by taking advantage of the undocumented stored procedure in SQL Server i.e. sp_MSforeachdb

DECLARE @query NVARCHAR(1000)

SET @query = 
'USE ?
IF DB_NAME() NOT IN (''master'', ''tempdb'', ''model'', ''msdb'')
SELECT COUNT(*) AS Count FROM ?.dbo.fin_MemberAccount'

EXEC sp_MSforeachdb @command1 = @query

-- ? : this means the current db while iterating through db by stored procedure

CodePudding user response：

If all target databases are located in one instance, this can be done using the string_agg function in the following way:

Declare @schema_name sysname = N'dbo'
Declare @table_name sysname = N'Aircraft'
Declare @max nVarChar(max) = ''
Declare @QueryText nVarChar(max)

Select @QueryText = String_Agg(Concat(@max, N'Select * From ', 
                                            QuoteName([name]), N'.',
                                            QuoteName(@schema_name),N'.', 
                                            QuoteName(@table_name), Char(10)),
                               Concat(N'UNION ALL',Char(10)))
From master.dbo.sysdatabases 
Where OBJECT_ID(Concat(QuoteName([name]),'.',
                       QuoteName(@schema_name),'.',
                       QuoteName(@table_name))) Is Not Null

Print @QueryText

Exec sp_executesql @QueryText