How to display only column names and nothing more for a table in MySQL and MariaDB

How to display only field names in SHOW COLUMNS

Let's look at a situation when in MySQL and MariaDB you need to get information not from the table, but about the table itself, for example, field names.

We have already looked at how to get a list of fields for any table; for this, an SQL query with the SHOW operator is used, for example:

SHOW COLUMNS FROM mydb.mytable;
SHOW FIELDS IN mytable FROM mydb;

For details, see the article: How to list columns in MySQL/MariaDB. How to check if a column exists in a table in PHP

This method allows you to get the names of table fields, along with their description. That is, additional information is displayed, such as the field type, default value, whether the value must be non-zero, etc.

But what if you only need to get the names of table fields and nothing more? There is a solution for this, but it is not possible to achieve this using standard means using SHOW COLUMNS FROM.

How to get table column names from information_schema.columns

However, the DBMS stores the column names of all tables in information_schema.columns and we can get them from there using the following construct:

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

In this construction, you need to replace two values:

• replace your_table with the name of the table for which you want to get the column names
• replace database_name with the name of the database in which the table whose columns we are getting resides

For example, I want to get the column names of the “user” table, which is in the “mysql” database, to do this, I need to execute the following SQL query:

SELECT column_name FROM information_schema.columns WHERE table_name = 'user' AND table_schema = 'mysql';

If you want to list the columns in the order in which they appear in the table, add ORDER BY ordinal_position to the query:

SELECT column_name FROM information_schema.columns WHERE table_name = 'your_table' AND table_schema = 'database_name' ORDER BY ordinal_position;

An example of a real SQL query:

SELECT column_name FROM information_schema.columns WHERE table_name = 'user' AND table_schema = 'mysql' ORDER BY ordinal_position;

How to display only the names of table columns without their characteristics and without additional characters

If you need to get only the names of table fields without unnecessary data, then it is quite possible that you will also be bothered by the characters and spaces that are used to draw the table. To display only the names of table columns and nothing else, run the mysql/mariadb client with three options: -r -s -N, for example:

mariadb -u root -s -r -N

And then execute the query shown in this article above.

As a result, only the names of the table columns will be displayed without unnecessary data and symbols. You can use these names in a script or copy them.

See also: How to display results in MySQL and MariaDB without a table and without extraneous characters