MySQL and Objects

MySQL and Objects

MySQL Admin Queries - How to Retrieve MySQL Objects The following contains information MySQL system queries to use to retrieve database information for MySQL objects such as databases, tables, views, users, indexes, functions, procedures, 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'