How to get all columns names for all the tables in MySQL

Question

Is there a fast way of getting all column names from all tables in MySQL  without having to list all the tables

User · Accepted Answer

select   from information schema columns where table schema    your db  order by table name ordinal position

User · Answer

On the offchance that it s useful to anyone else  this will give you a comma-delimited list of the columns in each table   SELECT table name GROUP CONCAT column name ORDER BY ordinal position  FROM information schema columns WHERE table schema   DATABASE   GROUP BY table name ORDER BY table name   Note   When using tables with a high number of columns and or with long field names  be aware of the group concat max len limit  which can cause the data to get truncated

User · Answer

Similar to the answer posted by  suganya this doesn t directly answer the question but is a quicker alternative for a single table   DESCRIBE column name

User · Answer

The question was        Is there a fast way of getting all COLUMN NAMES from all tables in MySQL  without having to list all the tables    SQL to get all information for each column  select   from information schema columns where table schema    your db  order by table name ordinal position   SQL to get all COLUMN NAMES  select COLUMN NAME from information schema columns where table schema    your db  order by table name ordinal position

User · Answer

I wrote this silly thing a long time ago and still actually use it now and then   https   gist github com kphretiq e2f924416a326895233d  Basically  it does a  SHOW TABLES   then a  DESCRIBE   on each table  then spits it out as markdown   Just edit below the  if name  and go  You ll need to have pymysql installed

User · Answer

lt  php          table    orders            query    SHOW COLUMNS FROM  table           if  output   mysql query  query                 columns   array                while  result   mysql fetch assoc  output                     columns      result  Field                endwhile          endif          echo   lt pre gt            print r  columns           echo   lt  pre gt      gt

User · Answer

To list all the fields from a table in MySQL   select      from information schema columns   where table schema    your DB name      and table name    Your tablename

User · Answer

SELECT   FROM information schema columns WHERE table schema   DATABASE   ORDER BY table name  ordinal position   Since I don t have enough rep to comment  here s a minor improvement  in my view  over nick rulez s excellent answer  replacing WHERE table schema    your db  with WHERE table schema   DATABASE

User · Answer

Piggybacking on Nicola s answer with some readable php   a   mysqli query  conn  select   from information schema columns where table schema    your db  order by table name ordinal position     b   mysqli fetch all  a MYSQLI ASSOC    d   array    foreach  b as  c       if  is array  d  c  TABLE NAME                d  c  TABLE NAME      array               d  c  TABLE NAME         c  COLUMN NAME      echo   lt pre gt   print r  d    lt  pre gt

User · Answer

it is better that you use the following query to get all column names   easily   Show columns from tablename

[mysql] How to get all columns' names for all the tables in MySQL?

Examples related to mysql

Examples related to database-schema

Examples related to database-metadata