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
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
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.
Many thanks!
Wow great.. Thanks 🙂
If you have a diagram already, you can convert it to data dictionary
hi Shalin, i have the diagram, how can i convert it to data dictionary?
Great staff bro
which staff? it’s ‘stuff’ bro..
Thanks, it’s work great.
Pingback : Swapping column values in MySQL table - Anil Labs