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?

    (SELECT statement shows Empty set if 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"

    Good idea, now my answer makes a better match to the question.

  • mico

    mico Correct answer

    9 years ago

    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.

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

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

    Do you mind rewording your question a little to make it clearer it is columns you are after?

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

    Little code snippets without explanation of how they solve the OP's problem are discouraged. Please try to add more details to your answer.

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

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM