How to Display MySQL Table Schema: A Guide

There are a couple of ways to reveal the schema of a MySQL table: the DESCRIBE and “SHOW CREATE TABLE` commands are pretty solid. This article explores these methods and tells you which is best for any given scenario.

How to use the DESCRIBE statement in MySQL?

To view a table’s structure, the DESCRIBE statement is your go-to command. Execute it using the following syntax:

DESCRIBE your_table_name;

Replace your_table_name with the name of the table whose schema you wish to see. This command will return essential details like column names, data types, and whether null values are allowed, offering a concise overview of the table’s schema.

How to use the SHOW COLUMNS command in MySQL?

For a similar outcome with additional flexibility, the SHOW COLUMNS command comes in handy. It lets you inspect a table’s schema and supports filtering for specific columns:

SHOW COLUMNS FROM your_table_name;

To focus on columns that match a certain pattern, use:

SHOW COLUMNS FROM your_table_name LIKE 'pattern';

How to use the INFORMATION_SCHEMA in MySQL?

For a deeper dive into the table’s schema, query the INFORMATION_SCHEMA.COLUMNS table. This is ideal for detailed schema analysis or for scripting and automation tasks:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';

Customize this query by replacing your_table_name and your_database_name with your specific details to fetch comprehensive metadata about the table’s columns.

View table constraints and indexes

To get insights into table constraints and indexes, use the SHOW INDEX FROM command:

SHOW INDEX FROM your_table_name;

This reveals information about the table’s indexes, including key names, column involvement, uniqueness, and other critical index attributes.