MYSQL - List specific columns

Question

I am trying to write a query which lists the names of the columns in an SQL table, however, I don't want all the columns - just specific ones. So, for example, if I was to put the COMMENT = 'test' for the columns which I want to list then I thought my query would be:

SHOW COLUMNS FROM `tbl_name` WHERE `COMMENT`='test'

This however throws an error.

Any ideas?

Thanks,

I don't think its possible. SHOW is not a SELECT. So no WHERE clause. — yunzen
– yunzen, Commented Oct 20, 2014 at 10:53

Gordon Linoff · Accepted Answer · 2014-10-20 10:55:24Z

4

I think you can do this using information_schema.columns:

select column_name
from information_schema.columns c
where table_name = 'tbl_name' and
      column_comment = 'test';

answered Oct 20, 2014 at 10:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joshnik Over a year ago

Thanks, this worked perfectly. I just looked up the information.schema and it means I can essentially categorise my columns now for different users. Thanks!

adimoise91 · Accepted Answer · 2014-10-20 11:02:14Z

0

I think that SHOW COLUMNS can't have the WHERE clause, but you can try this:

SHOW COLUMNS FROM (SELECT * FROM `tbl_name` WHERE `COMMENT`='test')

answered Oct 20, 2014 at 11:02

adimoise91

5681 gold badge8 silver badges27 bronze badges

Collectives™ on Stack Overflow

MYSQL - List specific columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related