Mysql - Union show tables?

Is there a开发者_JS百科ny way of listing all tables and columns names using UNION or a JOIN?

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%'

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%'