qwerty
qwerty

Reputation: 5246

Mysql - Union show tables?

Is there any way of listing all tables and columns names using UNION or a JOIN?

Upvotes: 1

Views: 14755

Answers (2)

kvista
kvista

Reputation: 5059

If you want all tables and columns in a schema, no need to use UNION and BIND, just joining the data in

  • information_schema.columns
  • information_schema.tables

will do the trick. See details on both at:

http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

An example query that would achieve the minimum of what appears to be your goal would be:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS

But again, joining the two based on table name might be needed -- depends on your precise goal.

If you just want the tables with UNION/BIND in the names and the columns with UNION/BIND in the names, two simple queries to do that would be:

SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '%UNION%' or TABLE_NAME LIKE '%BIND%'

and

SELECT TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%UNION%' or TABLE_NAME LIKE '%BIND%'

Upvotes: 5

Chandu
Chandu

Reputation: 82933

You can use INFORMATION_SCHEMA. The query below would be useful:

SELECT 'COLUMN' as Match_Type, 
       column_name as MATCH_NAME, 
       table_name, 
       table_schema
 FROM INFORMATION_sCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%UNION%' 
   OR COLUMN_NAME LIKE '%BIND%'
UNION ALL
SELECT 'TABLE' as Match_Type, 
        table_name  as MATCH_NAME, 
        table_name, 
        table_schema
 FROM INFORMATION_sCHEMA.TABLES
WHERE TABLE_NAME LIKE '%UNION%'
   OR TABLE_NAME LIKE '%BIND%'

Upvotes: 0

Related Questions