MySQL: Return all tables from 'select tables_names …'

Question

I am using:

SELECT table_name AS tables FROM information_schema.tables WHERE table_name LIKE 'address_%'

... to get a list of all table in a database. I was advised to use this method, rather than 'SHOW TABLES', see:

mysql: SHOW TABLES - define column name

... as I can name the column (the AS clause, above, which is not available to SHOW TABLES).

As expected, this gives me the following output:

tables  
address_13e625c01bea04b1d311
address_147e32243c710542fb43
address_3b046272656fa61d7550
address_4f83b2740fc4f038775a
etc.

If I try and change the SELECT statement to get ALL tables (not just those starting with 'address_') to any of the following:

SELECT table_name AS tables FROM information_schema.tables WHERE table_name LIKE '%'

SELECT table_name AS tables FROM information_schema.tables WHERE table_name LIKE ''

SELECT table_name AS tables FROM information_schema.tables

These ALL return:

tables  
CHARACTER_SETS
CLIENT_STATISTICS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
etc.

I don't know what these values are (database attributes by the looks of it), but it is certainly not a list of tables in the database (the 'address_' (from above) ones are not even there.

Does anybody know what 'SELECT table_name ...' statement I should use, bearing in mind I want to name the column (which is why I cannot use 'SHOW TABLES') to get a full list of tables in the given database?

Use SELECT * FROM information_schema.tables to see what the table contains. Then you can decide, what filters to apply. — Paul Spiegel
I would say SELECT table_name AS tables FROM information_schema — hackela
You might want to restrict the query to only look at the table_schema (ie your databases) that you are interested in. — P.Salmon

Graham Graham · Accepted Answer · 2017-09-14T13:56:52

Thank you for the suggestions.

Based on those, I got my answer, which is:

SELECT TABLE_NAME AS 'table' FROM information_schema.tables where TABLE_name like '%'

This just returns the table names, in a column named table.

Thank you all

MySQL: Return all tables from 'select tables_names …'

1 Answers