How to Show Table Structure (Schema) in MySQL

How to Show Table Structure (Schema) in MySQL

Published Jan 19,2023 by Kailash Singh

0 Comment     1608 Views    

In this post, we are going to teach you, How to Show Table Structure (Schema) in MySQL

Examle 1: SQL DESC statement use for describe the list of column definitions for specified table. You can use either DESC or DESCRIBE statement. both are return same result.

DESCRIBE statement to get following information:

• Column Name
• Column allow NULL or NOT NULL
• Datatype of the Column
• With database size precision and If NUMERIC datatype scale.

Syntax:

DESC Table_name;

Query: 

DESC users;

Note:  users is the table name of the database.

Result:

Example 2: Here is the another example, how to show the structure of the table with the help SHOW COLUMNS.

Syntax: 

SHOW COLUMNS FROM Table_name;

Query:

SHOW COLUMNS FROM users;

Example 3: If you want to show the complete structure of the table. Like- TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, DATATYPE, COMMENTS & so on.

Syntax:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Table_name';

Query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'users';