14

I'm learning SQL with MySQL 5.7. And I wanna enumerate all tables include temporary tables!

But when I query SHOW TABLES; It shows only non-temporary tables.

How can I list all tables?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
0xF4D3C0D3
  • 747
  • 1
  • 5
  • 15
  • https://stackoverflow.com/questions/7075483/is-there-a-way-to-get-a-list-of-all-current-temporary-tables-in-sql-server – atmd Jun 26 '17 at 06:50
  • https://stackoverflow.com/questions/25138810/list-temporary-table-columns-in-mysql – atmd Jun 26 '17 at 06:51
  • 1
    Possible duplicate of [List temporary table columns in mysql](https://stackoverflow.com/questions/25138810/list-temporary-table-columns-in-mysql) – atmd Jun 26 '17 at 06:52
  • 1
    umm.. I wanna get temporary tables not temporary table's columns. And in MySQL not SQL SERVER 2000. thank you – 0xF4D3C0D3 Jun 26 '17 at 06:56

2 Answers2

0

I've not found a direct answer to this - as far as I can see there is no way (currently MySQL 8.0.31) of listing all temporary tables on your connection.

You can test for a table's existence using:

SELECT 1 FROM my_table WHERE 0;

If you don't get an error, the table exists in some form (TEMPORARY, BASE TABLE or VIEW). To check if it's temporary you can use:

SHOW TABLES IN my_db
WHERE Tables_in_my_db = "my_table"

(returns the table name if it exists and not temporary or use 'FULL TABLES' to return the table type as well) or

SELECT table_type
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = "my_db" AND TABLE_NAME = "my_table";

(returns the table type if it exists and not temporary)

So a table is temporary if the first test doesn't return an error but it is NOT listed using one of the other tests.

This doesn't help to enumerate the temporary tables (I believe this can be done - see other answers - if the table is INNODB but not generally for MyISAM for example), but it does enable you to identify a table as temporary or otherwise if you know the name.

Thickycat
  • 894
  • 6
  • 12
-3

In innodb you could us e

 SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_TEMP%';

https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-temp-table-info.html

https://dev.mysql.com/worklog/task/?id=648

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • I read all your links. Umm.. then there are no way to enumerate temporary tables with name? not like #sql_xxxx. – 0xF4D3C0D3 Jun 26 '17 at 07:21
  • 7
    This statement lists the table that lists the temp tables. The correct statement would be `SELECT * FROM information_schema.INNODB_TEMP_TABLE_INFO;`. And no @0xF4D3C0D3, sadly not. – Matthew Read Mar 15 '18 at 20:04