CODEWITHSUNDEEP
mysqlsqloracle-database
Noah Martin
Noah Martin

Reputation: 1809

Include the table name in a select statement

I need to include the table name in a SELECT statement, together with some columns and the unique identifier of the table.

I don't know if there is possible to take the table name from a select within that table or some kind of unique identifier.

How can I achieve this?

Upvotes: 3

Views: 3554

Answers (3)

Noah Martin
Noah Martin

Reputation: 1809

I thank you for your responses but I fixed this in this way (it was too easy actually)

select 'table1' as tableName, col1, col2 from anyTable;

Upvotes: 2

mustaccio
mustaccio

Reputation: 18945

You will need to query the system catalog of the database to find the primary key and all unique constraints of the table, then choose one that best suites your needs. You can expect to find 0, 1, or more such constraints.

For an Oracle database you'd use something like

  select 
   c.constraint_name,
   col.column_name
  from
   dba_constrants c,
   dba_cons_columns col
  where
   c.table_name = 'YOURTABLE'
   and c.constraint_type in ('P', 'U')
   and c.constraint_name = col.constraint_name
  order by
   c.constraint_name, 
   col.position

For MySQL you would query INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE views in a similar manner.

Upvotes: 1

Hip Hip Array
Hip Hip Array

Reputation: 4753

this will give you all the table names from your database, you can tweak it as you see fit

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

Upvotes: 0

Related Questions