SQL: Replacing part of query by subquery in FROM clause

Question

I have a database including a table called [Table] which lists all tables in that database. Now I want to write a SQL query using some JOINS, which gets a specific tablename from [Table] in a subquery to select from that table... I hope this is not too confusing.

So [Table] looks like this:

IdTable    Tablename
1          Adress
2          Project
3          User
...

The query should look like this:

SELECT * FROM (SELECT Type FROM dbo.[Table] WHERE tablename = 12)

Would something like that be possible?

I know, that subqueries are possible, but I do not know how to do that in this case.

Thanks in advance.

Regards Lars

Why do you want to write something like this in the first place? What is the *actual* problem you try to do? Tables in a SQL query are like types in a strongly typed language. You can't just change them by passing a different string. On the other hand, sending a query from the client with the *correct* table is trivial. Creating views that query specific tables is also easy. Besides, mapping a number to a table name is no better than using the table name itself. Instead of `select * from SomeQueryFrom(1)` you could just write `select * from Address` — Panagiotis Kanavos, Jul 04 '19 at 15:50
`I have a database including a table called [Table] which lists all tables in that database.` SQL Server already provides the `sys.tables` table for this. You don't need to create an extra table. In fact, you can use the [OBJECT_NAME](https://learn.microsoft.com/en-us/sql/t-sql/functions/object-name-transact-sql?view=sql-server-2017) function to retrieve the table name from an object ID. You don't need an extra table for this. You could write `declare @sql nvarchar(2000)='SELECT * from ' + QUOTENAME(OBJECT_NAME(@someID)); exec @sql;` to create and execute a query based on a table's ID — Panagiotis Kanavos, Jul 04 '19 at 15:53

score 0 · Answer 1 · answered Jul 04 '19 at 15:53

Try this below dynamic code to get your expected result.

DECLARE @sqlCommand  AS NVARCHAR(MAX)
DECLARE @TableName AS NVARCHAR(MAX)

SELECT @TableName = tablename FROM dbo.[Table] WHERE Type = 12

SET @sqlCommand= N'SELECT * FROM '+@TableName+''

EXEC (@sqlCommand)

Note: I guess you wants to select TableName where Type = 12 and I alter the selection and filtering accordingly.

You can also execute the query directly as below without creating the command string-

DECLARE @TableName AS NVARCHAR(MAX)
SELECT @TableName = tablename FROM dbo.[Table] WHERE Type = 12

EXEC (N'SELECT * FROM '+@TableName+'')

Why not `SELECT @SQL = CONCAT(N'SELECT * FROM ', QUOTENAME(TableName)) FROM dbo.[Table] WHERE Type = 12; `. There is no need to `@TableName` variable. — Ilyes, Jul 04 '19 at 16:03

SQL: Replacing part of query by subquery in FROM clause

1 Answers1