Is it possible to select from "show tables" in MySQL?

Question

Well, yes, the title says it all.

SELECT * FROM (SHOW TABLES) AS my_tables

Something along these lines, though the above does not work (on 5.0.51a, at least).

Answer 1

Have you looked into querying INFORMATION_SCHEMA.Tables? As in

SELECT ic.Table_Name, ic.Column_Name, ic.data_Type, isnull(Character_Maximum_Length,'') Max, ic.Numeric_precision as Precision, ic.numeric_scale as Scale, ic.Character_Maximum_Length as VarCharSize, ic.is_nullable as Nulls, ic.ordinal_position as OrdinalPos, ic.column_default as ColDefault, ic.autoinc_next as Next, ku.ordinal_position as PK, kcu.constraint_name,kcu.ordinal_position, tc.constraint_type

FROM INFORMATION_SCHEMA.COLUMNS ic

left outer join INFORMATION_SCHEMA.key_column_usage ku on ku.table_name = ic.table_name and ku.column_name = ic.column_name

left outer join information_schema.key_column_usage kcu on kcu.column_name = ic.column_name and kcu.table_name = ic.table_name

left outer join information_schema.table_constraints tc on kcu.constraint_name = tc.constraint_name

order by ic.table_name, ic.ordinal_position;

Answer 2

I think you want SELECT * FROM INFORMATION_SCHEMA.TABLES

See http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

Answer 3

I think what you want is MySQL's information_schema view(s): http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

Answer 4

SELECT * FROM INFORMATION_SCHEMA.TABLES

That should be a good start. For more, check INFORMATION_SCHEMA Tables.

Answer 5

I don't understand why you want to use SELECT * FROM as part of the statement.

12.5.5.30. SHOW TABLES Syntax

Answer 6

Not that I know of, unless you select from INFORMATION_SCHEMA, as others have mentioned. However, the SHOW command is pretty flexible, e.g.:

SHOW tables like '%s%'

Answer 7

How about:

SELECT t.{fieldname} FROM (SHOW COLUMNS FROM {tablename}) as t;

I desperately need this to work