MySQL Admin - How to View MySQL Objects
The following contains information on how to retrieve database information for MySQL objects such as databases, tables, views, users, indexes, and constraints.
The method of retrieving system information from MySQL is dependent on the MySQL version. For MySQL versions 5 and newer, MySQL has followed the lead of the enterprise databases such as Oracle, DB2, and SQL Server by providing an information schema with tables that contain data relating to system information. Prior to MySQL 5, special queries needed to be executed to get this type of information. When applicable, both sets of queries will be listed.
This is a query to retrieve MySQL databases for MySQL 5 or newer:
select SCHEMA_NAME from information_schema.SCHEMATA
This is a query to retrieve MySQL databases for all versions of MySQL:
This is a query to retrieve MySQL tables for MySQL 5 or newer:
select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'mysql'
This is a query to retrieve MySQL tables for all MySQL versions:
The first step is to make sure you have a current database set. To set a database, the "use" command can be executed. For example,
The above command sets the current database to mysql.
The query to display the tables is the following:
This is a query to retrieve MySQL views for MySQL 5 or newer:
select TABLE_NAME from information_schema.VIEWS where TABLE_SCHEMA = 'mysql'
This is a query to retrieve MySQL users: select distinct user from mysql.user where user <> '' order by user
This is query to retrieve MySQL indexes for MySQL 5 or newer. Note: primary key indexes will all have the same name unless given
an explicit name when created.
select distinct INDEX_NAME from information_schema.STATISTICS where TABLE_SCHEMA = 'mysql'
This is a query to retrieve constraints for MySQL 5 or newer.
select distinct CONSTRAINT_NAME from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA = 'mysql'