How To Get Column Names in MySQL: Four Methods

In this tutorial, we will explore the various methods and techniques we can use to fetch the columns in a given MySQL table.

How To Get Column Names in MySQL: Four Methods

MySQL is one of the most influential relational databases of the modern age. Like any other relational database system, it stores data in a structured manner using tables, columns, rows, etc.

Each table in a MySQL databases contains a set of rows and columns where the columns represents the different attributes of the data stored in that table while the rows refer to the actual data in the table.

In some cases, especially when working with a large and complex database, you may need to extract or view the names of the columns in a particular table. This can allows you to quickly gather the information about the table structure, etc.

Method 1 - Using the DESCRIBE Command

The simplest and most common method of fetching the column names of a given table is the DESCRIBE command. This command returns the information about the columns in a table.

The statement syntax is as shown below:

DESCRIBE table_name;

For example, to get the columns in the film table, we can the query:


The command above will return a list of columns in the film table, along with their associated data type, whether NULL values are allowed, and other properties.

| Field                | Type                                                                | Null | Key | Default           | Extra                                         |
| film_id              | smallint unsigned                                                   | NO   | PRI | NULL              | auto_increment                                |
| title                | varchar(128)                                                        | NO   | MUL | NULL              |                                               |
| description          | text                                                                | YES  |     | NULL              |                                               |
| release_year         | year                                                                | YES  |     | NULL              |                                               |
| language_id          | tinyint unsigned                                                    | NO   | MUL | NULL              |                                               |
| original_language_id | tinyint unsigned                                                    | YES  | MUL | NULL              |                                               |
| rental_duration      | tinyint unsigned                                                    | NO   |     | 3                 |                                               |
| rental_rate          | decimal(4,2)                                                        | NO   |     | 4.99              |                                               |
| length               | smallint unsigned                                                   | YES  |     | NULL              |                                               |
| replacement_cost     | decimal(5,2)                                                        | NO   |     | 19.99             |                                               |
| rating               | enum('G','PG','PG-13','R','NC-17')                                  | YES  |     | G                 |                                               |
| special_features     | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES  |     | NULL              |                                               |
| last_update          | timestamp                                                           | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |

Method 2 - Using the SHOW COLUMNS Command

We can also use the SHOW COLUMNS command to get column information.

The command syntax is as shown:


For example:


The command above should return similar output to the describe command.


MySQL includes several built-in schema databases for maintaining information about your server and databases. One of these is the INFORMATION_SCHEMA, which includes a COLUMNS table that stores information about columns for all tables in all databases.

We can can use this table to get the list of columns associated with a given table as shown in the syntax below:

SELECT column_name 
FROM information_schema.columns
WHERE table_schema = 'your_database' 
AND table_name = 'your_table';

For example:

SELECT column_name 
FROM information_schema.columns
WHERE table_schema = 'sakila' 
AND table_name = 'film';

The command above will return a list of column names for the film table in the sakila database.

| COLUMN_NAME          |
| film_id              |
| title                |
| description          |
| release_year         |
| language_id          |
| original_language_id |
| rental_duration      |
| rental_rate          |
| length               |
| replacement_cost     |
| rating               |
| special_features     |
| last_update          |
13 rows in set (0.00 sec)

NOTE: Ensure to replace 'your_database' and 'your_table' with your database name and table name respectively.

Method 4 - Using SHOW CREATE TABLE Command

We also have the SHOW CREATE TABLE command which allows us to view the CREATE TABLE statement used to initialize the given table.

This is a way to get column names along with the entire table structure.

The command syntax is as shown


For example:


This command returns a long string with the SQL statement to create the table. The column names are embedded within this string.

film  | CREATE TABLE `film` (
  `film_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `description` text,
  `release_year` year DEFAULT NULL,
  `language_id` tinyint unsigned NOT NULL,
  `original_language_id` tinyint unsigned DEFAULT NULL,
  `rental_duration` tinyint unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),

This method is more useful if you're interested in other aspects of the table structure, not just the column names.


In this tutorial, we discussed the various methods you can use to get column names in MySQL. It's crucial to familiarize yourself with these commands as they can significantly speed up your database work and improve efficiency.

Table of Contents
Great! Next, complete checkout for full access to GeekBits.
Welcome back! You've successfully signed in.
You've successfully subscribed to GeekBits.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info has been updated.
Your billing was not updated.