How to show the column names of a table?
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?
Empty setif 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.
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"
select column_name from information_schema.columns where table_name='table'
Meanwhile I found out this query also useful: `show create table ` where instead of you put the table name in plain text. This gives also the types while your one suits for a listing purpose very well.
As said by @mico : "show create table tbl_name\G" might be your solution. See https://dev.mysql.com/doc/refman/5.7/en/show-create-table.html The "\G" part is here only to format output. Regards.
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;
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 | +------------+------------------+
I wanted those column names and have already a couple of ways thanks to fast answers. Thanks about this comment thing also, Jack.
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)
You need to join
information_schema.columnstogether to get the list of tables and their columns' details.
information_schema.columnsnot 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.
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 ;