Footsteps Generate Database Data dictionary

SQL Script Generation data dictionary

http://www.jianshu.com/p/f491d0d3c503

Word 735Read 207Reviews 0like 2

Many internet start-up companies are not too standard, new people come, not too many design documents, requirements documents, and no ER diagram, data dictionary these things. So, it takes time to get familiar with the business, many simple questions (like the meaning of state values in a datasheet) have to ask the old staff because there is no comment and no data dictionary.

After the question, I usually add comments in the test library, not to ask next time. Every time I go to a new company, I will sort out a data dictionary and ER diagram, one for convenience, and the other for new colleagues.

The first company is using a SQL Server 2008 database, and generating a data dictionary is cumbersome and difficult to understand:

Use Yourdatabase--Specify the database to generate the data dictionaryGo SELECT table name =case when a.colorder=1Then D.name else"'End, table description =case when a.colorder=1Then IsNull (F.value,"') Else"'End, field ordinal =a.colorder, field name =a.name, Identity =case when ColumnProperty (A.id,a.name,' isidentity ')=1Then' √ 'Else"'End, primary key =case when exists (SELECT1From sysobjects where xtype=' PK 'and name in (the select name from sysindexes where Indid in (select Indid from sysindexkeys where id = a.id and colid=a. colid)) Then' √ 'Else"'End, type =b.name, number of bytes =a.length, length =columnproperty (A.id,a.name,' PRECISION '), the number of decimal =isnull (ColumnProperty (A.id,a.name,' scale '),0), allow empty =case when a.isnullable=1Then' √ 'Else"'End, the default value =isnull (E.text,"'), field Description =isnull (G.[value],"') from syscolumns A to join systypes B on a.xtype=b.xusertype inner join sysobjects D on A.id=d.id and d.xtype=' U 'and d.name<>' dtproperties 'Left join syscomments E on a.cdefault=e.id left join Sys.extended_properties G on a.id=g.major_id and a.colid=g.minor_id Left join Sys.extended_properties F on d.id=f.major_id and f.minor_id =0  --where d.name= ' table to query '--if you only query the specified table, add this conditionORDER BY A.id,a.colorder

The second company uses MySQL5.6, which is simple to generate a data dictionary:

USE information_schema;

 select t . Table_schema as  , t . table_name as  , t . Table_type as  ,  

 t . engine  as  , c . Ordinal_position as  , c . column_name as  , 

 c . Column_type as  , c . is_nullable as  , c . Column_key as  ,  

 c . EXTRA as  , c . Character_set_name as   ' encoded name ' , c . Column_comment as    

 from  c  inner  join  tables  on  c . Table_schema = t . Table_schema and  c . table_name = t . TABLE_NAME 

WHERE    T. Table_schema =' MySQL '  --Specify the database to generate the data dictionary

As follows:

Data dictionary. png

These two scripts are just handy for viewing data dictionaries, you can export to Excel, you can write programs, connect to a database, generate HTML pages or formatted Excel, Word, and so on. I have encapsulated a jar package, just to configure the database address, user name, and password in the configuration file to generate the. doc document directly from the Java command.

Footsteps Generate Database Data dictionary