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'
Functions
This is a query to retrieve functions for MySQL 5 or newer.
SELECT
ROUTINE_NAME
FROM
information_schema.ROUTINES
WHERE
ROUTINE_TYPE = 'FUNCTION' and
ROUTINE_SCHEMA = 'sample'
Procedures
This is a query to retrieve procedures for MySQL 5 or newer.
SELECT
ROUTINE_NAME
FROM
information_schema.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE' and
ROUTINE_SCHEMA = 'sample'
Listed below are links to articles containing system queries for other databases:
Cassandra: Cassandra System Queries
Firebird: Firebird System Queries
Microsoft SQL Server: SQL Server System Queries
Oracle: Oracle System Queries
PostgreSQL: PostgreSQL System Queries
SQLite: SQLite System Queries
Sybase: Sybase System Queries
