MySQL Show Table Schema

“Relational databases are some of the most popular and influential databases of the modern digital age. Despite being developed in the 90s, they remain prevalent and are widely adopted by individual developers and enterprise applications.

Relational databases are well known for their use of a pre-defined schema. Schemas are the foundation of relational databases as they define the rules and structure of how data is organized before the actual data is added.

Learning how to build and structure your database schema is necessary as a database developer. And through this tutorial, we will walk you through how you can create and view a table schema using MySQL, one of the most popular relational database management systems.”

What is a Schema in Relational Databases?

A schema refers to the structure that defines how data in a relational database is organized. A schema is used to determine important aspects of the data, such as the columns, data type, and more.

In simple terms, think of a schema as the blueprint that lays out the foundation of the data organization and implementation. However, remember that the schema does not hold the actual data.

Type of Database Schemas

The following are common schema types in relational databases:

Tables
Primary keys
Foreign keys
Attributes
Relations

Create Table Schema

Before we discuss how to show the table schema, let us create a sample table for illustration purposes.

The sample code snippet:

create table blog(
post_id int auto_increment not null primary key,
author_id int not null,
title varchar(255) not null,
author_name varchar(100) not null,
post_date date not null,
category varchar(50) not null,
published bool
);

Advance to view the table schema.

Show Table Schema – Method 1 – During Table Creation

MySQL allows you to view the table schema using the SHOW keyword. The command syntax:

SHOW CREATE TABLE database.table_name;

The command will then return the commands used during the table creation.

To display the blog’s table schema (provided above), run the query:

show create table linuxhint.blog;

The show statement will output the result as shown:

|Table|Create Table |
|-----|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|blog |CREATE TABLE `blog` (¶ `post_id` int NOT NULL AUTO_INCREMENT,¶ `author_id` int NOT NULL,¶ `title` varchar(255) NOT NULL,¶ `author_name` varchar(100) NOT NULL,¶ `post_date` date NOT NULL,¶ `category` varchar(50) NOT NULL,¶ `published` tinyint(1) DEFAULT NULL,¶ PRIMARY KEY (`post_id`)¶) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci|

You will see the schema information of the defined table, including the collation, charset, and database engine. This is very useful when replicating the same table in a different environment.

Show Table Schema – Method 2 – Describe

The second and most common method of fetching the table schema is using the DESCRIBE keyword. The syntax is illustrated in the code snippet below:

DESCRIBE database.table_name;

As you can guess, the statement will return the table schema in a user-friendly UI.

Take the example snapshot below:

describe linuxhint.blog;

In this example, we are fetching the table schema of the blog table from the linuxhint database. Output:

|Field      |Type        |Null|Key|Default|Extra         |
|-----------|------------|----|---|-------|--------------|
|post_id    |int         |NO |PRI|       |auto_increment|
|author_id |int         |NO |   |       |              |
|title      |varchar(255)|NO |   |       |              |
|author_name|varchar(100)|NO |   |       |              |
|post_date |date        |NO |   |       |              |
|category   |varchar(50) |NO |   |       |              |
|published |tinyint(1) |YES |   |       |              |

Here, we can see that the data is presented in a tabular format making it very easy to read and interpret.

The drawback is that the output from the describe command cannot be used directly to replicate the table structure in a different environment.

NOTE: You can substitute the DESCRIBE command with EXPLAIN as shown in the syntax below:

EXPLAIN database.table_name;

NOTE: In some instances, the table schema refers to the database in which the specified table resides. For that, you can query the information_schema table as shown in the syntax below:

select TABLE_SCHEMA
from information_schema.TABLES t
where table_name = table_name;

The resulting output:

|TABLE_SCHEMA|
|------------|
|linuxhint |

Closing

In this article, we discussed database schemas in the relational database, types of database schemas, and creating and viewing the table structure in MySQL.

Thanks for reading!!