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

1

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

2

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

--exec(@SQL)

fetch next from SS into @database

end

close SS

Deallocate SS

1 Like
3 

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

This is brilliant - thanks :slight_smile: