Dynamically stating FROM which table name to SELECT from

Question

Would it be possible to use a system query to retrieve TABLE name and then SELECT * FROM that TABLE name. Along the lines of:

SELECT * FROM CAST (( SELECT TOP 1 t.Name 
                      FROM sys.tables t 
                      JOIN sys.columns c ON c.OBJECT_ID = t.OBJECT_ID 
                      WHERE c.NAME = 'SomeColumnID' )  AS sys.tables ) 

The current issue is that the SELECT TOP 1 t.Name will return a string and could it be then cast into a valid Tables.Name.

Answer 1

You need dynamic sql for this: that is, build a query string from a query, then execute it with sp_executesql.

For your use case, that would look like:

declare @q nvarchar(max);

select top (1) @q = N'select * from ' + t.name 
from sys.tables t 
join sys.columns c on c.object_id = t.object_id 
where c.name = 'SomeColumnID'

-- debuug the query
select @q sql;

-- execute the query
execute sp_executesql @q;