MySql: Show columns but exclude everything except the field names

Question

I'd like to pull a table's field names from MySql into python, and I know that

'show columns from project'

will work. And I've read that you can add 'WHERE ...' to restrict it to just certain fields. But I can't find an example of how to return just the names of the columns, and not Type, Key, Null, Extra information.

What is the matching criteria to pull all field names for columns and none of the other description stuff?

Related, if it's of interest, I wrote a *Describe All Tables* in [this Answer](http://stackoverflow.com/a/38679580). — Drew, Jul 31 '16 at 00:54

score 92 · Accepted Answer · edited Jan 08 '16 at 09:30

92

SELECT column_name
FROM information_schema.columns
WHERE  table_name = 'your_table'
   AND table_schema = 'database_name'

edited Jan 08 '16 at 09:30

Mark Peter Fejes

46
6

answered Apr 02 '11 at 21:25

Wonderful! I tested on phpMyAdmin on two servers. But there is only one information_schema database on each server, and potentially multiple tables in different databases on the save server that have the same table name. How does it know which list of table columns to pull in that case? – ChewyChunks Apr 03 '11 at 18:14
1

Check out the `table_schema` column – Apr 03 '11 at 19:47
For me it is working also without "AND table_schema = 'database_name'" – Peter Bakker Nov 02 '17 at 12:10
1

FYI, if you want to list the columns in the order they appear on the table, add `ORDER BY ordinal_position` to the query – Matt Humphrey May 11 '22 at 17:07

score 20 · Answer 2 · answered Feb 06 '13 at 15:18

Although it looks more elegant, you don't need awk for this. MySQL's information_schema.columns table has the info you need.

-- DESCRIBE THE HECK OUT OF THE ENTIRE 'table_name' table in the 'database_name' database

SHOW COLUMNS
FROM database_name.table_name ;

-- SHOW JUST THE COLUMN NAMES for 'table_name' table in the 'database_name' database.

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

score 3 · Answer 3 · edited Jun 01 '16 at 16:24

3

Pipe the answer to awk:

SHOW columns FROM project; | awk '{ print $1 }'

edited Jun 01 '16 at 16:24

Morten

2,148
2
15
16

answered Jan 31 '13 at 21:55

fsnow55

31
1

score 2 · Answer 4 · answered Apr 04 '17 at 11:57

2

You can query MySQL's information_schema database directly for the fieldnames:

select distinct(COLUMN_NAME) from information_schema.COLUMNS where TABLE_NAME='posts';

answered Apr 04 '17 at 11:57

Mahbub

4,812
1
31
34

score 1 · Answer 5 · answered Aug 15 '18 at 19:25

If your goal is a comma-delimited list (Not very Python literate, but that's mostly what you'd want in PHP and Perl) GROUP_CONCAT is your friend:

SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name='your-table-name-here' GROUP BY TABLE_NAME ORDER BY ORDINAL_POSITION

If you need them quoted this gives you all EXCEPT the outermost quotes:

SELECT GROUP_CONCAT(column_name SEPARATOR '","') FROM information_schema.columns WHERE table_name='your-table-name-here' GROUP BY TABLE_NAME ORDER BY ORDINAL_POSITION

Ali Nawaz · Answer 6 · 2013-02-08T07:12:12.860

-4

SHOW COLUMNS FROM `table_name`

This MySQL query will work best, it will show all fields of a MySQL table.

edited Feb 08 '13 at 07:12

answered Jan 29 '13 at 10:53

Ali Nawaz

1,006
10
11

MySql: Show columns but exclude everything except the field names

6 Answers6

Linked