MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Database Structure / Tables / Question No: 211

How to get table metadata?

Answer No: 211

You can ask MySQL to show you table metadata; that is, information about your tables. Metadata includes information such as table names or column or index definitions.

The INFORMATION_SCHEMA database has a TABLES table that contains table metadata. For example, to display information about a particular, use this statement:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='mytbl';

Information about indexes is available from INFORMATION_SCHEMA in the STATISTICS table.

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='mytbl';

MySQL also supports a family of SHOW statements that display metadata. Some that pertain to tables are SHOW TABLES and SHOW CREATE TABLE. To determine the tables that a particular database contains, use SHOW TABLES:

SHOW TABLES FROM mydb;
SHOW TABLES FROM mydb LIKE '%tr%';
SHOW CREATE TABLE table_name;

DESCRIBE is another statement that displays table structure metadata:

DESCRIBE table_name;

DESCRIBE table_name is a synonym for SHOW COLUMNS FROM table_name or SHOW FIELDS FROM table_name. These statements are equivalent:

DESCRIBE table_name
SHOW COLUMNS FROM table_name;
SHOW FIELDS FROM table_name;

You can also use SHOW to obtain index information. To find out what indexes a table has, use SHOW CREATE TABLE to display the CREATE TABLE statement that corresponds to the table structure, including its indexes. For more detailed information about the indexes, use SHOW INDEX. For example:

SHOW INDEX FROM table_name;

Related MySQL FAQs to the Above FAQ

How-to-create-table-in-MySQL How to create table in MySQL?

How-to-create-table-based-on-existing-tables How to create table based on existing tables?

What-is-temporary-table-and-how-to-create-it-in-MySQL What is temporary table and how to create it in MySQL?

How-to-describe-a-table-in-MySQL How to describe a table in MySQL?

How-to-renaming-a-table-in-MySQL How to renaming a table in MySQL?

How-to-specify-multiple-alterations-for-a-table How to specify multiple alterations for a table?

How-to-add-new-column-into-an-existing-table-in-MySQL How to add new column into an existing table in MySQL?

How-to-add-a-new-AUTO_INCREMENT-field-to-an-existing-table How to add a new AUTO_INCREMENT field to an existing table?

How-to-drop-a-column-from-the-existing-table How to drop a column from the existing table?

How-to-drop-all-existing-tables-in-one-go How to drop all existing tables in one go?

How-to-get-columns-of-an-existing-table-in-MySQL How to get columns of an existing table in MySQL?

How-to-change-data-type-of-an-existing-column How to change data type of an existing column?

How-to-delete-table-records How to delete table records?

How-to-set-AUTO_INCREMENT-value-other-than-1-in-MySQL How to set AUTO_INCREMENT value other than 1 in MySQL?

How-to-get-copy-of-table-structure-in-MySQL-using-query How to get copy of table structure in MySQL using query?

How-to-get-constraints-or-keys-of-the-existing-table How to get constraints or keys of the existing table?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.