sql server - SQL How to SELECT specific fields from tables using a table of table names -

so,

i trying find (messy?) solution more messy problem. have sql server 2014 database which, in part, stores data software package stores data me. software creates table specific fields each set of data - name , geometry field. example, 1 might contain cities (dtcitiesdata), contains roads (dtroadsdata), contains states(dtstates), etc. have table (dtspatialdatatables) stores names of tables store data want. table has 2 fields: id , tablename.

i create select statement queries dtspatialdatatables entries, queries tables name corresponding each tablename result, , selects name , geometry them.

in pseudocode, want this:

select tablename dtspatialdatatables  foreach tablename :     select name, geometry (tablename) 

i can php via first query against dtspatialdatatables , loop of queries each of returned row tablenames want know if possible via sql directly.

in reality, want create view query can directly query view rather soak of processing time on potentially lots of queries.

is possible? unfortunately, google-ing doesn't turn meaningful results.

thanks everyone!

ps: figure messy , not way should done. have no choice in how software puts data in database. have use get. so... whether "right" way or "wrong" way, need solution. :)

you using dynamic sql..

create procedure dbo.usp_spatialdata_getbyid (     @id int )    begin     declare @sql nvarchar(max),             @selects nvarchar(max) = 'select name, geometry, ''<<tablename>>'' source <<tablename>>'      select  @sql = coalesce(@sql + ' union ', '') + replace(@selects, '<<tablename>>', tablename)        dtspatialdatatables       id = @id      exec(@sql) end go 

i feel left out filtering of geometry tables somewhere might have add filter @selects statement