Q:

select * from '%_BT'.dbo.Version

I have a SQL Server 2008 database instance for a cloud product that I manage  product versions for.  
We format the use is
customername_bt
customername_bs

where [_bt] is product 1 and [_bs] is product 2.  I realise I can hard code then union a query like so;
select * from cust1_bt.dbo.version union
select * from cust1_bs.dbo.version union
select * from cust2_bt.dbo.version union
select * from cust2_bs.dbo.version

However, every time a new customer is added I'd have to revisit the query which adds an element of human error therefore, I'm wondering if I can achieve this result another way.
Obvious this isn't a thing but hopefully it drives what I'm hoping to achieve home
select * from '%_bt'.dbo.Version UNION
select * from '%_bs'.dbo.Version

Thanks in advance

A:

You can use dynamic sql
DECLARE @SQLText VARCHAR(MAX) =''

select @SQLText  = @SQLText  + 'UNION select * from ' + QUOTENAME(name) + '.dbo.Version ' 
from sys.databases WHERE 
   name like '%[_]bt' 
   or name like '%[_]bs' 

SET @SQLText = STUFF(@SQLText,1,6,'')

EXEC @SQLText

