MariaDB [mysql] 테이블 목록 조회 방법 및 nformation schema를 사용법
데이터베이스에 있는 테이블 리스트 조회
show tables;
show tables like ‘TB_%’;
다른 DB의 테이블 목록 조회하기
SHOW TABLES FROM DB명;
information schema를 이용하는 방법
SELECT TABLE_NAME
FROM information_schema.TABLES
WHERE TABLE_NAME LIKE 'TB_%';
DESC information_schema.TABLES;
information_schema를 이용하면 MySQL에 존재하는 다양한 정보를 확인할 수 있다.
MariaDB [mysql]> DESC information_schema.TABLES;
+------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | NULL | |
| TABLE_SCHEMA | varchar(64) | NO | | NULL | |
| TABLE_NAME | varchar(64) | NO | | NULL | |
| TABLE_TYPE | varchar(64) | NO | | NULL | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(64) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(2048) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | NULL | |
| MAX_INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| TEMPORARY | varchar(1) | YES | | NULL | |
+------------------+---------------------+------+-----+---------+-------+
23 rows in set (0.001 sec)
MariaDB [mysql]> SHOW TABLES FROM information_schema;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ALL_PLUGINS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEYWORDS |
| KEY_CACHES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| SQL_FUNCTIONS |
| SYSTEM_VARIABLES |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| CLIENT_STATISTICS |
| INDEX_STATISTICS |
| INNODB_FT_CONFIG |
| GEOMETRY_COLUMNS |
| INNODB_SYS_TABLESTATS |
| SPATIAL_REF_SYS |
| USER_STATISTICS |
| INNODB_TRX |
| INNODB_CMP_PER_INDEX |
| INNODB_METRICS |
| INNODB_FT_DELETED |
| INNODB_CMP |
| THREAD_POOL_WAITS |
| INNODB_CMP_RESET |
| THREAD_POOL_QUEUES |
| TABLE_STATISTICS |
| INNODB_SYS_FIELDS |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCKS |
| INNODB_FT_INDEX_TABLE |
| INNODB_CMPMEM |
| THREAD_POOL_GROUPS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_FT_INDEX_CACHE |
| INNODB_BUFFER_POOL_STATS |
| INNODB_FT_BEING_DELETED |
| INNODB_SYS_FOREIGN |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_SYS_TABLES |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_TABLESPACES |
| INNODB_SYS_INDEXES |
| INNODB_BUFFER_PAGE |
| INNODB_SYS_VIRTUAL |
| user_variables |
| INNODB_TABLESPACES_ENCRYPTION |
| INNODB_LOCK_WAITS |
| THREAD_POOL_STATS |
+---------------------------------------+
79 rows in set (0.000 sec)