How to list MySQL database table column names without the table formatting

If you want to list all of the MySQL database table column names (field names) as a simple list of names, with each column name listed on a separate line, just follow these steps.

First, start MySQL with the -sN options, like this:

$ mysql -sN -u root -p

Then execute a query like this:

mysql> SELECT column_name FROM information_schema.columns WHERE table_schema = 'YOUR_DATABASE_NAME' AND table_name = 'YOUR_TABLE_NAME';

For example, I just did this with a MySQL database named aa_d8 and a table named node__field_photo, and got this result:

mysql> SELECT column_name FROM information_schema.columns WHERE table_schema = 'aa_d8' AND table_name = 'node__field_photo';

bundle
deleted
entity_id
revision_id
langcode
delta
field_photo_target_id
field_photo_alt
field_photo_title
field_photo_width
field_photo_height

The query

Here’s that first query again, in a format that’s a little easier to copy and paste from:

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'YOUR_DATABASE_NAME' 
AND table_name = 'YOUR_TABLE_NAME';

The -sN options

From the MySQL help page, this is what the -sN options mean:

-s, --silent  Be more silent. Print results with a tab as separator,
              each row on new line.

-N, --skip-column-names
              Don't write column names in results.

If you don’t use -sN

Note that if you don’t use the -sN options when starting MySQL, you’ll see this output instead:

+-----------------------+
| column_name           |
+-----------------------+
| bundle                |
| deleted               |
| entity_id             |
| revision_id           |
| langcode              |
| delta                 |
| field_photo_target_id |
| field_photo_alt       |
| field_photo_title     |
| field_photo_width     |
| field_photo_height    |
+-----------------------+

All that extra table formatting and column header output is a pain if you only need the table column names listed, hence this solution.

Add new comment

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.