How to Specify Table Name as a String

Is there a way to specify table name as a string?

You can wrap it in an EXEC statement like this:

declare @my_tablename nvarchar(100) = 'mytable';
exec('
SELECT * FROM 
(
  SELECT * FROM 
  (
    SELECT * FROM ' + @my_tablename + '
  )
  INNER JOIN ' + @my_tablename + ' ON ...'
);

But no, intellisense will not work in that scenario.

If you know what your output will look like in advance, then you can declare a temp table to hold the results, and then you can access that without EXEC. You will have intellisense on the temp table.

For example:

  --this must match whatever your SELECT is going to return
  CREATE TABLE #results(
    FIELD1 INT
   ,FIELD2 NVARCHAR(100)
   ,FIELD3 BIT
   );

EXEC('
  INSERT INTO #results(field1,field2,field3)
  SELECT FIELD1,FIELD2,FIELD3 FROM ' + @my_tablename
);

select * from #results  --you will have intellisense on #results

A table name as a variable

For static queries, like the one in your question, table names and column names need to be static.

For dynamic queries, you should generate the full SQL dynamically, and use sp_executesql to execute it.

Here is an example of a script used to compare data between the same tables of different databases:

Static query:

SELECT * FROM [DB_ONE].[dbo].[ACTY]
EXCEPT
SELECT * FROM [DB_TWO].[dbo].[ACTY]

Since I want to easily change the name of table and schema, I have created this dynamic query:

declare @schema sysname;
declare @table sysname;
declare @query nvarchar(max);

set @schema = 'dbo'
set @table = 'ACTY'

set @query = '
SELECT * FROM [DB_ONE].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + '
EXCEPT
SELECT * FROM [DB_TWO].' + QUOTENAME(@schema) + '.' + QUOTENAME(@table);

EXEC sp_executesql @query

Since dynamic queries have many details that need to be considered and they are hard to maintain, I recommend that you read: The curse and blessings of dynamic SQL

Building SQL query string using table-name as given parameter

String building (prone to SQL injection)

What khelwood means:

def selectFrom(table):
    return 'SELECT * FROM ' + table

def see_results(cur, table):
    print("complete")
    cur.execute(selectFrom(table))
    results = cur.fetchall()
    print(results)

or even using f-strings cur.execute(f"SELECT * FROM {table}" directly.

But what if there is malicious input in passed argument table like an appended DROP or TRUNCATE statement (SQL injection)?

Query building (safer)

Using SQL capable libraries (SQL framework or database-frontend) like psycopg, you can build the SQL using safe methods which apply input-validation.

See the examples in module psycopg2.sql to compose an SQL-statement for a given table parameter.

from psycopg2 import sql

cur.execute(
    sql.SQL("SELECT * FROM {} WHERE values IN (%s, %s)")
        .format(sql.Identifier('my_table')),
    [10, 20])

Dynamic Query : How to define Table Name or other String in SQL-Server

Problem is here:

N'@TABLENAME nvarchar',

This code means that variable @TABLENAME is nvarchar(1). Change it to e.g.

N'@TABLENAME nvarchar(45)',

Also you have to delete double ' from INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = ''@TableName''.
So your code will look like:

DECLARE @sql as nvarchar(max)
set @sql = 'select *  from jfa.[dbo].[MyTable]  
INNER JOIN INFORMATION_SCHEMA.COLUMNS   on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TableName --change here
and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 2'

    exec sp_executeSQL @sql,
    N'@TABLENAME nvarchar(45)', --change here
    @TABLENAME

However, if you want to dynamically change table from which you are selecting data, then you have to change static jfa.[dbo].[MyTable] to jfa.[dbo].'+@TableName+'. Finally, your query will look like:

DECLARE @sql as nvarchar(max)
set @sql = 'select *  from jfa.[dbo].'+@TableName+'  --change here 
INNER JOIN INFORMATION_SCHEMA.COLUMNS   on INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = @TableName --change here
and INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION = 2'

    exec sp_executeSQL @sql,
    N'@TABLENAME nvarchar(45)', --change here
    @TABLENAME

How can I get the name of a table as a string in kdb+

idea is to save the table on the hard disk with the same filename as the name of the table.

To get a list of tables defined, use tables

e.g.

tables`. //for the root name-space

you can filter with like

e.g.

t:tables`.
t:t where t like "pattern_*"

and then save with save

save each hsym t //careful

SQL Command with string as Tablename

You don't need ' or % signs in the query, just go with name:

  Adapter = new OleDbDataAdapter("SELECT * FROM [" + tablename + "]", Connection);

Check msdn to be sure about syntax.

String table name not working in a query with psycopg2

when you quote something,it is considered as one object while "d1dbforest.interface.v_monitoring_ind34a4" is referring to 3 objects , database name , schema name and view name :

so you need to quote them separately: "d1dbforest"."interface"."v_monitoring_ind34a4"

so:

select "country","year","data","use" from "d1dbforest"."interface"."v_monitoring_ind34a4"

then you have to specify it in the identifier :

query = sql.SQL("select {fields} from {table}").format(fields=sql.SQL(',').join([
    sql.Identifier(country_column_id),
    sql.Identifier(time_column_id),
    sql.Identifier(value_column_id),
    sql.Identifier(type_column_id),
]),
table=sql.Identifier(db_name,schema_name,table_name))

reference: https://www.psycopg.org/docs/sql.html#module-psycopg2.sql

Create Table SQL query - select table name from string

Change the dashes to underscores or surround the entire table name with [square brackets]

---

Related Topics