How can I retrieve the column names of a table in mysql?

I have a table with n columns.

I want to retrieve the first 3 column names of that table.

Should be something like this:

Select column_name(3) from table_name="sample";

Asuming you have:

CREATE TABLE `new_table` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `create` text,
  `proname` varchar(45) DEFAULT NULL,
  `prodb` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Here is the query:

select 
    COLUMNS.* 
from information_schema.COLUMNS 
where table_schema='PRUEBA' 
    and TABLE_NAME='new_table'
ORDER BY ORDINAL_POSITION
LIMIT 0,3;

This query will show you the first 3 columns of the table new_table in the schema PRUEBA.

Result:

mysql> select 
    -> COLUMNS.* 
    -> from information_schema.COLUMNS 
    -> where table_schema='PRUEBA' 
    -> and TABLE_NAME='new_table'
    -> ORDER BY ORDINAL_POSITION
    -> LIMIT 0,3;
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------------+------------+----------------+---------------------------------+----------------+
| 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 | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE               | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------------+------------+----------------+---------------------------------+----------------+
| def           | PRUEBA       | new_table  | id          |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 | NULL               | NULL              | int(10) unsigned zerofill | PRI        | auto_increment | select,insert,update,references |                |
| def           | PRUEBA       | new_table  | create      |                2 | NULL           | YES         | text      |                    65535 |                  65535 |              NULL |          NULL | latin1             | latin1_swedish_ci | text                      |            |                | select,insert,update,references |                |
| def           | PRUEBA       | new_table  | proname     |                3 | NULL           | YES         | varchar   |                       45 |                     45 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(45)               |            |                | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+---------------------------+------------+----------------+---------------------------------+----------------+
3 rows in set (0.01 sec)

mysql>

Hope this help.

StackExchange Database Administrators Q#105762, answer score: 5