mysql

How to generate Data dictionary in MySql

In this post I am going to explain about how to generate Data dictionary in MySql database. In the earlier posts we learned about installation of MySql, import and export databases , MySql table schema, MySql database backup and MySql views. We can have simple query which will in below code and change database name with yours then you will get data dictionary of your database in MySql.

How to generate Data dictionary in MySql by Anil Kumar Panigrahi

How to generate Data dictionary in MySql by Anil Kumar Panigrahi

MySql Code:

select t.table_schema as db_name,  
t.table_name,  
(case when t.table_type = 'BASE TABLE' then 'table'
when t.table_type = 'VIEW' then 'view' 
else t.table_type  
end) as table_type,
c.column_name, 
c.column_type, 
c.column_default,  
c.column_key,  
c.is_nullable, 
c.extra,   
c.column_comment   
from information_schema.tables as t
inner join information_schema.columns as c 
on t.table_name = c.table_name 
and t.table_schema = c.table_schema
where t.table_type in('base table', 'view')
and t.table_schema = '<database name>' 
order by t.table_schema, t.table_name, c.ordinal_position

Note: < database name > change it with your database name.

8 Comments

  • Reply
    Chris C
    July 4, 2015 at 8:54 pm

    Many thanks!

  • Reply
    Janaka
    January 16, 2016 at 10:26 am

    Wow great.. Thanks 🙂

  • Reply
    Shalin
    June 6, 2016 at 9:03 am

    If you have a diagram already, you can convert it to data dictionary

    • Reply
      mayte
      June 4, 2018 at 9:39 pm

      hi Shalin, i have the diagram, how can i convert it to data dictionary?

  • Reply
    Chatura
    October 12, 2016 at 9:37 am

    Great staff bro

    • Reply
      tyler
      July 3, 2018 at 4:01 am

      which staff? it’s ‘stuff’ bro..

  • Reply
    Paco
    December 23, 2016 at 1:01 pm

    Thanks, it’s work great.

  • Reply
    Swapping column values in MySQL table - Anil Labs
    December 7, 2017 at 10:49 am

    […] How to generate Data dictionary in MySql […]

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.