table_name

Alternatives to Extract Tables and Columns from MySQL and MariaDB

I’ve previously published a post on extracting table names when /or/i was filtered which leads to filtering of the word information_schema. I did some more research into this area on my own and found many other tables where you can extract the table names. These are all the databases and tables I found where we can extract table names apart from ‘information_schema.tables’. I have tested the following in 5.7.29 MySQL and 10.3.18 MariaDB. There are 39 queries in total.

Sys

These views were added in MySQL 5.7.9.

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT object_name FROM `sys`.`x$innodb_buffer_stats_by_table` WHERE object_schema = DATABASE();
+-------------+
| object_name |
+-------------+
| emails      |
| flag        |
| referers    |
| uagents     |
| users       |
+-------------+
5 rows in set (0.04 sec)

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT TABLE_NAME FROM `sys`.`x$schema_flattened_keys` WHERE TABLE_SCHEMA = DATABASE();
+------------+
| TABLE_NAME |
+------------+
| emails     |
| flag       |
| referers   |
| uagents    |
| users      |
+------------+
5 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SELECT TABLE_NAME FROM `sys`.`x$ps_schema_table_statistics_io` WHERE TABLE_SCHEMA = DATABASE();
+------------+
| TABLE_NAME |
+------------+
| db         |
| emails     |
| flag       |
| referers   |
| uagents    |
| users      |
+------------+
6 rows in set (0.04 sec)

(more…)