How to Get Column Names in MySQL?

To get column names in MySQL use techniques such as the DESCRIBE statement, INFORMATION_SCHEMA.COLUMNS, and SHOW COLUMNS FROM commands.

Here will cover these techniques, with explained examples, and help to get a better understanding on how to get column names in MySQL.

MySQL Fetch Column Names from a Table

MySQL provides several methods to retrieve column names from a table. Whether you’re a beginner or an experienced developer, understanding these methods can strengthen your workflow and improve your database management skills.

MySQL offers simple methods to retrieve column names from tables.

• Using DESCRIBE statement
• Using INFORMATION_SCHEMA.COLUMNS Statement
• Using SHOW COLUMNS FROM Command

Using DESCRIBE statement to Get Column Names

To get column names, we mainly use the DESCRIBE statement. This command returns a list of columns in the specified table, along with their types and other details.

Syntax

DESCRIBE table_name;

Example

Suppose we have a table named `jobs` with columns `id `, `jobtitle`, `company`, `location`, `experienceInYear `, `salaryInLPA`, and `jobdescription`. Using DESCRIBE, we get:

Jobs Table for example

DESCRIBE jobs;

Output:

Output using DESCRIBE

The output contains follows:

• Field: Its displays the column names (`id`, `jobs` with columns `id `, `jobtitle` , `company` , `location` , `experienceInYear `, `salaryInLPA`, `jobdescription`).
• Type: It Indicates the data type of each column (int(11), varchar(50)).
• Null: It specifies whether the column allows NULL values (YES or NO).
• Key: This Indicates if the column is part of any key (e.g., PRI for primary key).
• Default: This Shows the default value for the column.
• Extra: Provides additional information, such as auto-increment for the id column.

Using INFORMATION_SCHEMA.COLUMNS Statement to Get Column Names

Another approach to retrieve column names is by querying the INFORMATION_SCHEMA.COLUMNS table. This system table contains metadata about columns in all databases accessible to the MySQL server.

Syntax

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘write_your_tablename_here’;

Example

Alternatively, you can use the INFORMATION_SCHEMA.COLUMNS table. Here’s how:

SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'jobs';

Output:

Output of example 2

The output contains a single column named column_name, which holds the names of columns from the jobs table. Each row in the result set represents a column name (`id`, `jobs` with columns `id `, `jobtitle` , `company` , `location` , `experienceInYear `, `salaryInLPA`, `jobdescription`) from the specified table.

Using SHOW COLUMNS FROM Command to Get Column Names

Another way to get the column names of a table, you use the SHOW COLUMNS FROM command in MySQL. DESCRIBE and these commands (both) return a result set with the columns. It is Similar to the DESCRIBE statement, which offers a quick way to display column information for a specified table.

Syntax:

SHOW COLUMNS FROM table_name;

Example

To get the column names of the `jobs` table using SHOW COLUMNS FROM, you can execute in the following way:

SHOW COLUMNS FROM jobs;

Output: 

Output using SHOW COLUMNS FROM method

The output contains the same as the DESCRIBE Statement.

Conclusion

In conclusion, retrieving column names from MySQL tables is essential for effective database management. The DESCRIBE statement, INFORMATION_SCHEMA.COLUMNS and SHOW COLUMNS FROM commands offer convenient methods for accessing column names. 

Whether obtaining details about table structure or querying metadata, these approaches provide users with flexibility and choice, contributing to improved data manipulation and query efficiency in MySQL databases.