CODEWITHSUNDEEP
sqlsql-servert-sqlselectdynamic-sql
Faris Kapo
Faris Kapo

Reputation: 376

Dynamically stating FROM which table name to SELECT from

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.

Upvotes: 0

Views: 56

Answers (1)

GMB
GMB

Reputation: 222672

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;

Upvotes: 2

Related Questions