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.

Databases

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:

show databases

 

Tables

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,

use mysql

The above command sets the current database to mysql.

The query to display the tables is the following:

show tables

 

Views

This is a query to retrieve MySQL views for MySQL 5 or newer:

select TABLE_NAME from information_schema.VIEWS where TABLE_SCHEMA = 'mysql'

 

Users

This is a query to retrieve MySQL users: select distinct user from mysql.user where user <> '' order by user

 

Indexes

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'

 

Constraints

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'