How To Get Column Names in MySQL
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.
In this tutorial, we will explore the various methods and techniques we can use to fetch the columns in a given MySQL table.
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:
DESCRIBE film;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:
SHOW COLUMNS FROM table_name;For example:
SHOW COLUMNS FROM film;The command above should return similar output to the describe command.
Method 3 - Using the INFORMATION_SCHEMA.COLUMNS Table
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
SHOW CREATE TABLE table_name;For example:
SHOW CREATE TABLE film;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,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
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.
Termination
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.
