Show MySQL table columns


Problem

Maybe you want to create the next database management tool or you are a terminal fan and just want to know more about the structure of a table.

Either way, there are some nice tools to do this.



Solution

Assuming you've got a table called persons you can simply run a query like this:

SHOW COLUMNS FROM persons

This will show you a table like this:

Field Type Null Key Default Extra
id int(11) YES
name varchar(50) YES
age int(11) YES

SHOW COLUMNS allows you to add condition to list columns matching a certain pattern. Here's an example that only lists the columns that contain the letter a:

SHOW COLUMNS FROM persons LIKE '%a%'

You can also query the information_schema instead:

SELECT * 
FROM information_schema.columns 
WHERE table_schema = database()
AND table_name = 'persons'

Comments




Please sign-in to post a comment