Query the same table across multiple databases on the same SQL instance

The SQL I wanting to run is: select *, strIssConfigInfo_FD from DBA.IntegratedSalesSystems_TB where nIssIsActive_FD
= 1

I would like to run this once per SQL instance across databases on that SQL instance to get the results.
Ideally if I can include the database name that would be good too

declare @database as varchar(255)

declare SS cursor for

select name from sys.databases

open SS

fetch next from SS into @database

While (@@FETCH_STATUS = 0 ) begin

Declare @SQL as varchar(max)
set @SQL = ' select *, strIssConfigInfo_FD from '+@database+'.DBA.IntegratedSalesSystems_TB where nIssIsActive_FD = 1'

Print @SQL


fetch next from SS into @database


close SS

Deallocate SS

1 Like

EXEC sp_msforeachdb 
'USE [?]; 
PRINT ''DBName='' + DB_Name()
IF EXISTS (SELECT * FROM sys.tables WHERE name = ''IntegratedSalesSystems_TB'')
SELECT	[DBName] = DB_Name(), 
FROM	DBA.IntegratedSalesSystems_TB
WHERE	nIssIsActive_FD = 1
1 Like

This is brilliant - thanks :slight_smile: