19

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.)

0

7 Answers 7

37
select column_name from information_schema.columns where table_name='table'
0
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.

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;
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

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 |
+------------+------------------+
0
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)

0
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 
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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