Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The case is simple: You have an mysql database where you have only 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.)

share|improve this question
    
edit: I made the question more clear. Thanks Jack for pointing the question to be not clear. –  mico Sep 29 '11 at 10:47
    
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

5 Answers 5

up vote 0 down vote accepted

Best solution that I found out by myself is desc table_name command. More information on this link. This command gives the description of one database table, which is exactly what I was trying to find out.

share|improve this answer

To make sure you list columns in a table in the current database, use the DATABASE() or SCHEMA() function. It returns NULL is 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;
share|improve this answer

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 |
+------------+------------------+
share|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 Douglas Sep 29 '11 at 10:25
2  
+1 just for mentioning the fact that comments exist ... not enough people use them, and it's so nice to find them when you're taking over a database. –  Joe Sep 30 '11 at 13:54
select column_name from information_schema.columns where table_name='table'
share|improve this answer
1  
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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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