What is a good way to document what your tables and columns in a SQL Server database mean?

Question

I am working on a C # project using a SQL Server 2008 database. There are more than 50 tables in the database and only on behalf of these tables and the columns inside them are not immediately obvious. I want to create some form of documentation so that future team members know what columns and tables do.

I'm looking for the equivalent of SQL Server C # "code comments" or "XML documentation for a method" - something new can look in to get an idea of ​​a database table or column.

What are some options?

Answer 1

You can add advanced properties.

View advanced properties

eg. displays all advanced properties in the AdventureWorks2008R2 sample database

USE AdventureWorks2008R2; GO SELECT class, class_desc, major_id, minor_id, name, value FROM sys.extended_properties; GO 

Entertainment with advanced features in SQL Server 2008

Answer 2

A simple approach for documenting table columns is to use the SQL Server Management Studio table designer. Fill in the Description field for each column.

Then you can view the table information with a query joining the sys.extended_properties table:

 -- list table columns SELECT OBJECT_NAME(c.OBJECT_ID) [TableName], c.name [ColumnName], t.name [DataType], x.Value [Description], c.max_length [Length], c.Precision ,c.Scale FROM sys.columns AS c JOIN sys.types AS t ON c.user_type_id=t.user_type_id LEFT JOIN sys.extended_properties x on x.major_id = c.object_id and x.minor_id = c.column_id WHERE OBJECT_NAME(c.OBJECT_ID) = 'Employees' ORDER BY c.OBJECT_ID, c.column_id; 

Answer 3

Extended properties ? It can be installed in the code or in the graphical interface

We also use the Red Gate SQL Doc to publish our schema, and advanced properties are displayed in the descriptions here. Very useful.

Answer 4

Documentation is a whole set of skills. Good naming methodologies are good, as well as consistency, so there is no change in the database regarding your style.

Besides using SQL charts or something else, best of all is a good document that explains tables and columns. Define the general structure and relationships between objects / tables / columns. Explain if the concepts are more complex, but make it easy for someone to say, “What does this column do?” and quickly find the answer without reading a paragraph or two to understand.

Answer 5

Are you ready to investigate (and possibly invest later) a separate tool for this work?

If so, look at these two:

• Red-Gate SQL Doc (view their sample AdventureWorks HTML doc on the web)
• ApexSQL doc

Both are not free - they cost a little money in advance, but both create excellent quality documentation in HTML, PDF, CHM format - your choice. They also get every small piece of information from the database - all the relationships between objects, constraints, and default values ​​for columns are many .

In my opinion, it is definitely worth the initial investment if you are doing any serious work.