tsql - Dynamic Query with if and UNION ALL -

i trying write select statement(s) insert data table. need select data databases starting 'centers' , tables in databases starting 'accthist'.i using cross join possible combinations of databasename.tablename. however, since such combinations not valid, trying check if table exists before select. added part of dynamic query... union between if statements. possible select cte valid db.table combinations? without doing in dynamic query?

 declare @tsql nvarchar(max)  set @tsql = ''   ;with cte_dbtabnames   (      select d.name dbname,t.name tbname       sys.databases d       cross join sys.tables t       d.name 'centers%'       , t.name  'accthist%'  )    select @tsql = @tsql + case len(@tsql) when 0 '' else ' union '  end +     'if object_id(''['+ dbname + '].dbo.['+ tbname + ']'') not null       begin '+ ' select * [' + dbname + '].dbo.['+ tbname + '] end'    cte_dbtabnames   select @tsql  exec(@tsql) 

you need use system tables generate dynamic sql based on tables found in each database. code should started.

declare @sql nvarchar(max) = ''  select @sql = @sql + 'select databasename = ''' + name + ''', * [' + name + '].sys.tables t t.name ''accthist%'' union ' sys.databases d name 'centers%'  set @sql = left(@sql, len(@sql) - 10)  select @sql --uncomment exec line below when comfortable dynamic sql correct. --exec sp_executesql @sql 

wiki