Search code examples
mysqlsqlinformation-schema

return table name and schema in sql query

I need to return the table name and schema name in my query and was unsure of how to do that without using information_schema or if I needed to join on information_schema for desired results. I'm using a mysql database and would like the table and schema within the results of the query below.

SELECT 
transfer_ID
, name 
, mapping
, records
, email_address
, locked
, active
, NOW() tNow
FROM data_transfer.Transfers DT

I would like to return the table name and schema name within the query above similar to this example.

SELECT table_name, 
FROM information_schema.tables
WHERE table_schema = 'db5'

Is this possible without having to specify table_schema?

Desired results would be:
name
mapping
record1
test@email
N
Y
timestamp
data_transfer
Transfers

Solution

  • Just add new columns with the values since you know them at the time the query is run.. It's not like this is Dynamic SQL and the schema and table names change at run time is it?

    SELECT 
transfer_ID
, name 
, mapping
, records
, email_address
, locked
, active
, NOW() tNow
, 'data_transfer' as schemaName
, 'Transfers' as tableName
FROM data_transfer.Transfers DT