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.

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.