2

I am doing a mySQL 'SHOW TABLES' with a 'LIKE'. For example:

show TABLES like 'address_%'

... to return all tables that start with 'address_'. That works fine.

The table name that is returned has a column name of

Tables_in_username_users (address_%)

Is there a way to define the name of this column in the 'SHOW TABLES" command (to say 'address tables')?

This is not only easier but if I change the 'like' search criteria the column name changes and this is problematical when processing the result in PHP as the like value is the name in the associative array and I would prefer to have a known column name?

TO give a specific example, as requested below:

=== Edited Question ===

Apolgies for any confusion.

If I say:

show TABLES like 'address_%'

I get a list of tables returned. That is:

Tables_in_username_addresses (address_%)    
address_13e625c01bea04b1d311
address_147e32243c710542fb43
address_4f83b2740fc4f038775a

My questions is, how do I dictate the name of the column name and not let it be called 'Tables_in_username_addresses (address_%)'

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Graham
  • 129
  • 1
  • 8
  • Can you give a more specific example? It's not clear what you're trying to deal with here in terms of actual names. – tadman Sep 13 '17 at 15:45
  • I think answer on your question is here - https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them – yAnTar Sep 13 '17 at 15:47
  • Possible duplicate of [How to find all the tables in MySQL with specific column names in them?](https://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them) – yAnTar Sep 13 '17 at 15:50
  • Apolgies for any confusion. If I say: show TABLES like 'address_%' I get a list of tables returned. That is: Tables_in_username_addresses (address_%) address_13e625c01bea04b1d311 address_147e32243c710542fb43 address_4f83b2740fc4f038775a My questions is, how do I dictate the name of the column name and not let it be called 'Tables_in_username_addresses (address_%) 'G – Graham Sep 13 '17 at 15:53

1 Answers1

3

You could query the information schema yourself:

SELECT table_name AS address_tables
FROM   information_schema.tables
WHERE  table_name LIKE 'address_%';
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thanks - but I was looking specifically at the 'SHOW TABLES' command. I have tried to clarify by editing the question. – Graham Sep 13 '17 at 16:04
  • Ahhh - this post - https://stackoverflow.com/questions/6925949/how-to-do-show-tables-as-col-alike-stuff says it is impossible (AFAIK) – Graham Sep 13 '17 at 16:07
  • ... so you were right. Still be interested to know if it is impossible with 'SHOW TABLES" – Graham Sep 13 '17 at 16:08
  • Indeed, AFAIK this is impossible. But `show tables` is just a syntactic sugar for querying information_schema. Why not query it directly? – Mureinik Sep 13 '17 at 16:08
  • 1
    Thx - because I was not sure what the 'table_name' should be. I thought it was a table name :-). But I have just tried what you said above and it worked a treat. Thanks you! – Graham Sep 13 '17 at 16:13