How to show the column names of a table?

Question

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

Answer 1

select column_name from information_schema.columns where table_name='table'

Answer 2

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.

Answer 3

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;

Answer 4

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

Answer 5

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

Answer 6

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)

Answer 7

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 
;