MySQL Export data dictionary using Navicat

Original works, allow reprint, please be sure to use hyperlinks in the form of the original source of the article, author information and this statement. Otherwise, the legal liability will be investigated. http://suifu.blog.51cto.com/9167728/1851898

Data dictionary is a DBA need to maintain the important content, some people like to use Excel to maintain, I prefer to directly in the production library maintenance, so that the production library to ensure that the comments are the latest content, in order to facilitate the flow of production library data dictionary, will be used in Excel to record the way, This article records how to quickly export data dictionary information from a production library to Excel.

This is a little trick.

Using the columns table in MySQL's information_schema

and the export function in Navicat for fast Export data dictionary

CREATE TEMPORARYTABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT‘‘,
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT‘‘,
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT ‘‘,
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT‘‘,
  `ORDINAL_POSITION` bigint(21) unsigned NOTNULL DEFAULT ‘0‘,
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT ‘‘,
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT ‘‘,
  `CHARACTER_MAXIMUM_LENGTH` bigint(21)unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsignedDEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsignedDEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULTNULL,
  `DATETIME_PRECISION` bigint(21) unsignedDEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULTNULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT ‘‘,
  `EXTRA` varchar(30) NOT NULL DEFAULT ‘‘,
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT ‘‘,
  `COLUMN_COMMENT` varchar(1024) NOT NULLDEFAULT ‘‘
) ENGINE=MyISAMDEFAULT CHARSET=utf8;

For example, I need to export the data dictionary information for the Guifan library and the test library:

Select Table_schema,table_name,column_type,column_comment frominformation_schema.columns where TABLE_SCHEMA= ' Guifan ' or table_schema= ' test '

In the top right corner of the query results page, there is a button to export the function, click to select a variety of export formats, as shown in

Follow the instructions to export

OK, this is the data dictionary I want.

At this point, the data dictionary is exported quickly.

This article is from the "Age volt" blog, please make sure to keep this source http://suifu.blog.51cto.com/9167728/1851898

MySQL Export data dictionary using Navicat