18

The case is simple: You have a MySQL database where you have only an SQL query interface and you want to know the database structure with queries. You can list tables with show tables; command, but how do you see the individual column names?

(SELECT statement shows Empty set if no data is present and can NOT be thus used.)

  • Please note, the accepted solution is the bottom one about desc command. Curious, it is the shortest, but has least votes. For me, also it is the best one. – mico May 10 '13 at 7:50
  • 2
    you asked for a simple SQL query. Your answer is not SQL, so that may explain the low number of votes. You should probably change the title of your query to: "How to show the column names of a table" – Lennart Dec 9 '16 at 18:01
  • Good idea, now my answer makes a better match to the question. – mico Dec 9 '16 at 18:04
37
select column_name from information_schema.columns where table_name='table'
| improve this answer | |
  • 2
    Meanwhile I found out this query also useful: show create table <table_name> where instead of <table_name> you put the table name in plain text. This gives also the types while your one suits for a listing purpose very well. – mico Sep 29 '11 at 9:49
  • As said by @mico : "show create table tbl_name\G" might be your solution. See dev.mysql.com/doc/refman/5.7/en/show-create-table.html The "\G" part is here only to format output. Regards. – tdaget Mar 2 '17 at 7:43
7

The best solution that I found out by myself was the desc table_name command. More information is on List MySQL Tables. This command gives the description of one database table, which is exactly what I was trying to find out.

| improve this answer | |
6

To make sure you list columns in a table in the current database, use the DATABASE() or SCHEMA() function. It returns NULL if you are not in a current database. This query will show the columns in a table in the order the columns were defined:

SELECT column_name,column_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name='table'
ORDER BY ordinal_position;
| improve this answer | |
3

I'm not sure I understand what you mean with table titles, but you can get most of the information about tables and their columns from the INFORMATION_SCHEMA

| improve this answer | |
3

how do you see the individual table titles?

Do you mean table comments?

use stack;
create table t(v integer primary key) comment 'My Special Table';

show tables;
+-----------------+
| Tables_in_stack |
+-----------------+
| t               |
+-----------------+

select table_name, table_comment from information_schema.tables where table_name='t';
+------------+------------------+
| table_name | table_comment    |
+------------+------------------+
| t          | My Special Table |
+------------+------------------+
| improve this answer | |
  • I wanted those column names and have already a couple of ways thanks to fast answers. Thanks about this comment thing also, Jack. – mico Sep 29 '11 at 10:18
  • 1
    Do you mind rewording your question a little to make it clearer it is columns you are after? – Jack says try topanswers.xyz Sep 29 '11 at 10:25
2
SHOW COLUMNS FROM mydb.mytable;

where mydb - is the database that contains needed table

mytable - is the needed table

It return info of columns (for examp. names of columns, type, e t c)

| improve this answer | |
  • 1
    Little code snippets without explanation of how they solve the OP's problem are discouraged. Please try to add more details to your answer. – Erik Dec 9 '16 at 17:48
0

You need to join information_schema.tables and information_schema.columns together to get the list of tables and their columns' details.

information_schema.columns not only shows detail about tables but also views. There is no way to filter only table details from this system view.

Hence you need to join.

Example query:

select t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_TYPE, c.DATA_TYPE
from information_schema.tables t , information_schema.columns c where 2=2 
and t.table_schema=c.table_schema
and t.table_name=c.table_name
and t.table_type ='BASE TABLE' 
order by t.table_schema, t.table_name, c.column_name 
;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.