Get table name from database field

Question

I use SQL Server Management Studio 17.0. I have a table which controls separate tables for different files, something like:

 filename | tablename
 ---------+----------
 file1    | table1
 file2    | table2

I need to select from tablename, but not hardcoded. Filename comes from web, and I can fist get the tablename, like

select tablename 
from filetables 
where filename = "file1"

and use it to view the file table:

select * 
from (table1)

Is there any way to do it in SQL? Something like

Select * 
from 
    (select tablename 
     from filetables 
     where filename = "file1")

Show source
| sql-server   | sql   2017-09-05 22:09 2 Answers

Answers to Get table name from database field ( 2 )

  1. 2017-09-05 22:09

    You can't select from a table that can only be determined at run time and/or depending on some parameter. Your only option is to use a Dynamic SQL in this case but make sure you don't expose yourself to SQL-Injection attacks.

    Here's a link on how to safely create Dynamic SQL.

  2. 2017-09-05 23:09
       DECLARE @v_sql        NVARCHAR(MAX),
               @v_table_name NVARCHAR(MAX),
               @v_file_name  NVARCHAR(MAX)
    
        SELECT @v_file_name  = 'file1' -- Populated from web proc
    
        SELECT @v_table_name = tablename
          FROM filetables
         WHERE filename = REPLACE(@v_file_name, '''', '') 
    
        SELECT @v_sql =
    
       'SELECT *
          FROM ' + @v_table_name + '
         WHERE filename = ''' + @v_file_name + ''''
    
        SELECT @v_sql -- Debug code to show you the statement prior to running
    
        EXEC sp_executesql @v_sql
    

    You will need to utilize dynamic SQL like other users here have answered. Give this a shot in your environment and see how it goes.

Leave a reply to - Get table name from database field

◀ Go back