特定のデータベース上にて特定のテーブル名を含むテーブルを一覧で表示する方法。


mysql> SHOW TABLES FROM test LIKE '%TBL%';
+------------------------+
| Tables_in_test (%TBL%) |
+------------------------+
| TBL1 |
| TBL2 |
+------------------------+
2 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS FROM test LIKE '%TBL%'\G
*************************** 1. row ***************************
Name: TBL1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-21 14:18:44
Update_time: 2009-07-21 14:18:44
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: TBL2
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 2533274790395903
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-21 14:20:29
Update_time: 2009-07-21 14:20:29
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
2 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE '%TBL%'\G

*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: TBL1
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 1970324836974591
INDEX_LENGTH: 1024
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2009-07-21 14:18:44
UPDATE_TIME: 2009-07-21 14:18:44
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: TBL2
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 2533274790395903
INDEX_LENGTH: 1024
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2009-07-21 14:20:29
UPDATE_TIME: 2009-07-21 14:20:29
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
2 rows in set (0.00 sec)

mysql>

show_table