Re: Getting only the column names
From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Fri, 29 Jul 2016 15:33:22 -0400
Message-ID: <nngb1m$kne$1_at_jstuckle.eternal-september.org>
>
> 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)
>
Date: Fri, 29 Jul 2016 15:33:22 -0400
Message-ID: <nngb1m$kne$1_at_jstuckle.eternal-september.org>
On 7/29/2016 2:44 PM, Gordon Burditt wrote:
>> 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.
>
> 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)
>
Gordon,
[Quoted] Good point. I don't use this table because it's non-standard and not guaranteed to remain the same format. But if the OP is just dealing with a limited number of versions of MySQL, this will work.
[Quoted] But I also never found it hard to parse DESCRIBE TABLE (a pretty simple REGEX, even for me :) ), and it is portable across most databases.
-- ================== Remove the "x" from my email address Jerry Stuckle jstucklex_at_attglobal.net ==================Received on Fri Jul 29 2016 - 21:33:22 CEST