Six database document methods, tools and practices of ASP. NET web development framework

ArticleDirectory

• SQL Doc
• Management table description
• Custom table

As the project progresses, more and more database tables and fields are frequently changed. How to document these changes is both convenient and maintainability. For example, you can create Word documents for all tables to describe the meaning of each table and the meaning of fields. However, if the database table or field changes frequently, updating the Word documents is a relatively cumbersome task. In the blog, a technician created a document generation toolProgramDirectly generate WORD Documents, which makes it much more feasible to create documents for the database. You can easily click the mouse to complete the work, which is very easy.

SQL Doc

In the toolbox of database documentation recommended to you, the top one is the SQL Doc of Redgate.

Create a new project, connect to the database server, select a database, and create a description for the table or its fields in the right pane.

Use the SQL profiler query tracker and check that it performs the following SQL statements.

 Exec Sp_executesql n'If (select count (*) from: fn_listextendedproperty (@ propertyname, @ mylevel0type, @ mylevel0name, @ mylevel1type, @ mylevel1name, @ mylevel2type, @ mylevel2name)> 0 begin exec success @ propertyname, @ propertyvalue, @ success, @ mylevel0name, @ mylevel1type, @ mylevel1name, @ mylevel2type, @ mylevel2name; end else begin exec success @ propertyname, @ propertyvalue, @ mylevel0type, @ mylevel0name, @ mylevel1type, @ mylevel1name, @ mylevel2type, @ mylevel2name; end' , N '@ Propertyname nvarchar (14), @ mylevel0type nvarchar (6), @ mylevel0name nvarchar (3), @ mylevel1type nvarchar (5), @ mylevel1name nvarchar (10 ), @ mylevel2type nvarchar (6), @ mylevel2name nvarchar (6), @ propertyvalue nvarchar (4 )' , @ Propertyname = N 'Ms _ description' , @ Mylevel0type = N 'Scheme' , @ Mylevel0name = N'Dbo' , @ Mylevel1type = N 'Table' , @ Mylevel1name = N 'Adbbsysmsg' , @ Mylevel2type = N 'Column' , @ Mylevel2name = N 'Recnum' , @ Propertyvalue = N 'Record number' 

It creates remarks for tables or fields and stores them directly in the database.

Go back to SQL Server Management studio and check that it is directly attached to the extended attribute of the field.

The convenience is not just here. When you right-click a table and generate a table creation script using script table as, its last row will add attributes.

 Create   Table [DBO]. [adbbsysmsg] ([recnum] [ Int ] Identity (1, 1) Not   Null , [Sysmsg_id] [nvarchar] (16) Not   Null , [Message] [nvarchar] (255) Not   Null , [Created_date] [datetime] Not   Null , [Created_by] [nvarchar] (10) Not   Null , [Revised_date] [datetime] Not   Null , [Revised_by] [nvarchar] (10)Not   Null , Constraint [Pk_adbbsysmsg] Primary   Key   Clustered ([Sysmsg_id] ASC ) With (Pad_index = Off , Statistics_norecompute = Off , Ignore_dup_key = Off , Allow_row_locks = On , Allow_page_locks = On , Fillfactor = 70) On [ Primary ]) On [ Primary ] Go  Exec SYS. sp_addextendedproperty @ name = N 'Ms _ description' ,@ Value = N 'Record number' , @ Level0type = N 'Scheme' , @ Level0name = N 'Dbo' , @ Level1type = N 'Table' , @ Level1name = N 'Adbbsysmsg' , @ Level2type = N 'Column' , @ Level2name = N 'Recnum' Go

So far, this is the best database documentation tool I have found. The new version 2.1 supports SQL Server 2008 R2.

 

Management table description

Before I found the SQL Doc, I kept searching for a way to create documents for the database, which is easy to maintain. After exploration, I created the following tools to maintain the database documentation.

The principle of this tool, like the preceding SQL doc, also applies extended attributes. Key part C #CodeAs shown below

    String Tablesql = @ "If (select count (1) From fn_listextendedproperty (N 'Ms _ description '," + "N 'schema', n' {2} ', n' table', n' {0} ', null, null)> 0" + "Exec sp_updateextendedproperty n 'Ms _ description', n' {1 }'," + "N 'schema', n' {2} ', n' table', n' {0} ', null, null" + "Else" + "Exec sp_addextendedproperty n 'Ms _ description', n' {1 }'," + "N 'schema', n' {2} ', n' table', n' {0 }'" ; // Update the field description {2}. The default value is DBO, but a large number of adventureworks fields are not DBO.          String Fieldsql =@ "If (select count (1) From fn_listextendedproperty (N 'Ms _ description', N 'scheme '," + "N' {3} ', n' table', n' {0} ', n' column', n' {1} ')> 0" + "Exec sp_updateextendedproperty n 'Ms _ description', n' {2 }'," + "N 'schema', n' {3} ', n' table', n' {0} ', n' column', n' {1 }'" + "Else" + "Exec sp_addextendedproperty n 'Ms _ description', n' {2 }'," + "N 'schema', n' {3} ', n' table', n' {0} ', n' column', n' {1 }'" ; // Read the table description          String Gettabledescription ="Select value from" + "Fn_listextendedproperty (N 'Ms _ description', N 'schema', n '{1}', N 'table '," + "N' {0} ', null, null )" ; // Read the description of the field          String Getfielddescription = "Select objname, value from" + "Fn_listextendedproperty (N 'Ms _ description', N 'schema', n '{1}', N 'table '," + "N' {0} ', n' column', null )" ;

Tablesql comments the table and adds extended attributes. fieldsql adds extended attributes to fields. The following two rows are used for reading. The most important part of the entire tool is here, which is tracked by the query tracker. Although SQL Server Management studio can be used directly to complete this task, it is not intuitive to use this tool to make it easy to maintain.

 

Custom table

This method is conservative and stable. Create a data table in the database to store the meaning of the table and its fields. Then, design a tool to update and maintain the table. The Kingdee system is used as an example to apply this method.

To query the document information of a table and its fields, run the following statement:

 
Select*FromT_tabledescriptionSelect*FromT_fielddescription
 

 -- to query a specific table description, run the following statement:  select  *  from  t_fielddescription  where  ftableid = ( select  ftableid  from  t_tabledescription  where  ftablename =  't_ item' ) 

In addition, the auxiliary tool of the Gold disc is also simple and practical to view the table description.

There are many product lines of the Gold disc, and the use of different product data tables varies. For example, this is applicable to clothing, shoes, and hats.

This method is simple and intuitive, and the description information is directly stored in the table. You need to provide an update tool to read and view the tool. If you can export it to word/Excel, it will be more perfect.