The information schema is a standard set of read-only views that provide metadata about objects in a database. These views are typically accessed by qualifying the view name with INFORMATION_SCHEMA (for example INFORMATION_SCHEMA.TABLES). But before we do this, we need to know which views are available in the first place.
One way to get a list of the information schema views available in MySQL is to check the MySQL documentation. Of course, you’ll need to be sure that you’re looking at the documentation that corresponds with your version of MySQL, just in case there have been any changes.
Another way to get a full list of the information views is with the SHOW TABLES statement.
Example
Here’s an example of getting a full list of the INFORMATION_SCHEMA views on my system:
SHOW TABLES FROM INFORMATION_SCHEMA;Result:
+---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | ADMINISTRABLE_ROLE_AUTHORIZATIONS | | APPLICABLE_ROLES | | CHARACTER_SETS | | CHECK_CONSTRAINTS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLLATIONS | | COLUMN_PRIVILEGES | | COLUMN_STATISTICS | | COLUMNS | | COLUMNS_EXTENSIONS | | ENABLED_ROLES | | ENGINES | | EVENTS | | FILES | | INNODB_BUFFER_PAGE | | INNODB_BUFFER_PAGE_LRU | | INNODB_BUFFER_POOL_STATS | | INNODB_CACHED_INDEXES | | INNODB_CMP | | INNODB_CMP_PER_INDEX | | INNODB_CMP_PER_INDEX_RESET | | INNODB_CMP_RESET | | INNODB_CMPMEM | | INNODB_CMPMEM_RESET | | INNODB_COLUMNS | | INNODB_DATAFILES | | INNODB_FIELDS | | INNODB_FOREIGN | | INNODB_FOREIGN_COLS | | INNODB_FT_BEING_DELETED | | INNODB_FT_CONFIG | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_DELETED | | INNODB_FT_INDEX_CACHE | | INNODB_FT_INDEX_TABLE | | INNODB_INDEXES | | INNODB_METRICS | | INNODB_SESSION_TEMP_TABLESPACES | | INNODB_TABLES | | INNODB_TABLESPACES | | INNODB_TABLESPACES_BRIEF | | INNODB_TABLESTATS | | INNODB_TEMP_TABLE_INFO | | INNODB_TRX | | INNODB_VIRTUAL | | KEY_COLUMN_USAGE | | KEYWORDS | | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | RESOURCE_GROUPS | | ROLE_COLUMN_GRANTS | | ROLE_ROUTINE_GRANTS | | ROLE_TABLE_GRANTS | | ROUTINES | | SCHEMA_PRIVILEGES | | SCHEMATA | | SCHEMATA_EXTENSIONS | | ST_GEOMETRY_COLUMNS | | ST_SPATIAL_REFERENCE_SYSTEMS | | ST_UNITS_OF_MEASURE | | STATISTICS | | TABLE_CONSTRAINTS | | TABLE_CONSTRAINTS_EXTENSIONS | | TABLE_PRIVILEGES | | TABLES | | TABLES_EXTENSIONS | | TABLESPACES | | TABLESPACES_EXTENSIONS | | TRIGGERS | | USER_ATTRIBUTES | | USER_PRIVILEGES | | VIEW_ROUTINE_USAGE | | VIEW_TABLE_USAGE | | VIEWS | +---------------------------------------+ 79 rows in set (0.00 sec)
The SHOW TABLES statement is used to show a list of tables from the given database. In our case, we got a list of tables from the INFORMATION_SCHEMA database.
Filtering the Results
We can narrow down the results if we’re only interested in certain tables:
SHOW TABLES
FROM INFORMATION_SCHEMA
LIKE 'table%';Result:
+---------------------------------------+ | Tables_in_information_schema (TABLE%) | +---------------------------------------+ | TABLE_CONSTRAINTS | | TABLE_CONSTRAINTS_EXTENSIONS | | TABLE_PRIVILEGES | | TABLES | | TABLES_EXTENSIONS | | TABLESPACES | | TABLESPACES_EXTENSIONS | +---------------------------------------+ 7 rows in set (0.00 sec)
We can also exclude certain views from the list:
SHOW TABLES
FROM INFORMATION_SCHEMA
WHERE Tables_in_information_schema NOT LIKE 'innodb%';Result:
+---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | ADMINISTRABLE_ROLE_AUTHORIZATIONS | | APPLICABLE_ROLES | | CHARACTER_SETS | | CHECK_CONSTRAINTS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLLATIONS | | COLUMN_PRIVILEGES | | COLUMN_STATISTICS | | COLUMNS | | COLUMNS_EXTENSIONS | | ENABLED_ROLES | | ENGINES | | EVENTS | | FILES | | KEY_COLUMN_USAGE | | KEYWORDS | | OPTIMIZER_TRACE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | RESOURCE_GROUPS | | ROLE_COLUMN_GRANTS | | ROLE_ROUTINE_GRANTS | | ROLE_TABLE_GRANTS | | ROUTINES | | SCHEMA_PRIVILEGES | | SCHEMATA | | SCHEMATA_EXTENSIONS | | ST_GEOMETRY_COLUMNS | | ST_SPATIAL_REFERENCE_SYSTEMS | | ST_UNITS_OF_MEASURE | | STATISTICS | | TABLE_CONSTRAINTS | | TABLE_CONSTRAINTS_EXTENSIONS | | TABLE_PRIVILEGES | | TABLES | | TABLES_EXTENSIONS | | TABLESPACES | | TABLESPACES_EXTENSIONS | | TRIGGERS | | USER_ATTRIBUTES | | USER_PRIVILEGES | | VIEW_ROUTINE_USAGE | | VIEW_TABLE_USAGE | | VIEWS | +---------------------------------------+ 48 rows in set (0.00 sec)
In this example I returned all views except for those that start with INNODB.