How to show the column names of a table with a simple sql query?

Question

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

Answer 1

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

Answer 2

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

Answer 4

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;

Answer 5

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.