0

I have a table dbo.TableNames with a varchar column TableName containing table names:

    |    Id     | TableName             |
    +-----------+-----------------------+
    |    1      | dbo.BookDictionary    |     
    |    2      | dbo.AnotherDictionary |

What I am curious about, is there a way to instead of writing:

SELECT * 
FROM dbo.BookDictionary

write something like:

SELECT * 
FROM 
    (SELECT TableName FROM dbo.TableNames WHERE Id = 1)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jan J.
  • 45
  • 5
  • Yes, but you have to use Dynamic SQL for this. Have a look at https://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query – squillman Dec 10 '21 at 15:39
  • 2
    If you *are* storing table names like this I suggest storing the schema and table name separately. Your table isn't called `dbo.BookDictionary` it's called `BookDictionary` and it's on a schema called `dbo`. Though this has the strong smell of an [XY Problem](//xyproblem.info). – Thom A Dec 10 '21 at 15:48

2 Answers2

0

If there is any chance that your TableNames values could be controlled by the end-user, you'll want to carefully verify the value retrieved to avoid a potential SQL Injection attack.

DECLARE @TableName nvarchar(max);
SELECT @TableName = TableName FROM dbo.TableNames WHERE ID = @ID;
If @TableName Is Null RAISERROR('Table %d does not exist.', 16, 1, @ID);

DECLARE @VerifiedSchemaName sysname, @VerifiedTableName sysname;

SELECT
    @VerifiedSchemaName = S.name,
    @VerifiedTableName = T.name
FROM
    sys.tables As T
    INNER JOIN sys.schemas As S
    ON S.schema_id = T.schema_id
WHERE
    @TableName = S.name + N'.' + T.name
;

If @@ROWCOUNT = 0 RAISERROR('Table "%s" does not exist.', 16, 1, @TableName);

DECLARE @sql nvarchar(max);
SET @sql = CONCAT('SELECT * FROM ', 
    QUOTENAME(@VerifiedSchemaName), 
    N'.', 
    QUOTENAME(@VerifiedTableName), 
    N';');

EXEC(@sql);
Richard Deeming
  • 29,830
  • 10
  • 79
  • 151
-1

It is possible but i wouldnt recommend it, because it will result in performance drops quit fast

declare @sql varchar(max)

SELECT @sql = CONCAT('SELECT * FROM ', [TableName], ';') 
FROM [dbo].[TableNames] 
WHERE Id = 1

sp_executesql @sql
Isparia
  • 682
  • 5
  • 20
  • 1
    Careful, this is open to injection attacks if the values in the column `TableName` are not *very* tightly controlled. Also your call to `sp_executesql` won't work as it's not the first statement in the batch and the parameter data type being wrong also generate an error. – Thom A Dec 10 '21 at 15:46