Re: Getting only the column names

From: Gordon Burditt <gordonb.ig8tq_at_burditt.org>
Date: Fri, 29 Jul 2016 13:44:04 -0500
Message-ID: <Q9idnfx2PuZpPgbKnZ2dnUU7-VPNnZ2d_at_posted.internetamerica>

> Is there any way to query a table to get only the column *names*?
> Like, not DESCRIBE TABLE, I want only the *names* of the columns.
> Preferably in a table itself which I can query.

[Quoted] Recent (and even not-that-recent) versions of MySQL have the built-in information_schema database. This is an example from MySQL 5.6.30. Pay particular attention to table_schema (= database name), table_name, and column_name).

mysql> describe information_schema.columns;

+--------------------------+---------------------+------+-----+---------+-------+


| Field                    | Type                | Null | Key | Default | Extra |


+--------------------------+---------------------+------+-----+---------+-------+


| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |

| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |

| TABLE_NAME               | varchar(64)         | NO   |     |         |       |

| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |

| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |

| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |

| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |

| DATA_TYPE                | varchar(64)         | NO   |     |         |       |

| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |

| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |

| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |

| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |

| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |

| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |

| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |

| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |

| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |

| EXTRA                    | varchar(30)         | NO   |     |         |       |

| PRIVILEGES               | varchar(80)         | NO   |     |         |       |


| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |


+--------------------------+---------------------+------+-----+---------+-------+

20 rows in set (0.00 sec)

And here's an example of getting information on the table 'tv.titles'. If you only want the names, you only need to select column_name, but various other information is available.

mysql> select * from information_schema.columns where table_schema = 'tv' and table_name = 'titles';

+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+


| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT |


+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+


| def           | tv           | titles     | series      |                1 | NULL           | YES         | varchar   |                        4 |                      4 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(4)  | MUL        |       | select,insert,update,references |                |

| def           | tv           | titles     | prod        |                2 |                | NO          | varchar   |                       10 |                     10 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(10) | MUL        |       | select,insert,update,references |                |


| def           | tv           | titles     | title       |                3 |                | NO          | varchar   |                       80 |                     80 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(80) |            |       | select,insert,update,references |                |


+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+

3 rows in set (0.00 sec) Received on Fri Jul 29 2016 - 20:44:04 CEST