3

I have many tables in my MySQL database & I want to know the table structure of all tables.

When I type desc tableName in SQL Column I see the table structure in tabular format. How can I get the table structure in SQL query format as we normally see?

Answer

I found answer in below link.

https://dba.stackexchange.com/questions/15537/how-to-display-database-structure-in-phpmyadmin

Community
  • 1
  • 1
Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
  • ***@CloseVotersForDuplicate :*** **This is not duplicate of [this](http://stackoverflow.com/questions/898688/how-to-get-database-structure-in-mysql-via-query) because if you write `DESCRIBE table;` in phpMyAdmin, you will get table structure in tabular format and not in the query format how we get on command prompt...** – Fahim Parkar Jun 09 '13 at 07:12
  • Related, if it's of interest, I wrote a *Describe All Tables* in [this Answer](http://stackoverflow.com/a/38679580). You could mess with the output you are looking for (for instance, don't have it call the 2nd stored proc by having parameter #4 being FALSE). – Drew Jul 31 '16 at 00:52

2 Answers2

2

Do you mean:

desc TABLENAME\G;
markdwhite
  • 2,360
  • 19
  • 24
  • \g does nothing different from desc TABLENAME; but \G formats the output differently. Just try it and see if it's what you need. – markdwhite Jun 08 '13 at 13:52
  • 2
    **desc company_details \G** gives syntax error... – Fahim Parkar Jun 08 '13 at 13:55
  • Yup what @FahimParkar indicated, that appended `\G` gave me an error as well: `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\G' at line 1` – fusion27 May 28 '14 at 17:23
  • Got error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\G' at line 1 – Mystery Jan 02 '23 at 15:05
  • @Mystery - without really wanting to resurrect a necro thread, this might help https://stackoverflow.com/questions/45878588/unable-to-describe-table-error-1064-42000-you-have-an-error-in-your-sql-synt – markdwhite Jan 04 '23 at 00:21
0

Or you can,

SHOW COLUMNS FROM ``MyDataBase`.`MyTables` WHERE IN
( SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'MyDataBase' );
Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276
anders
  • 4,168
  • 2
  • 23
  • 31