How to document SQL Server database objects

The guide covers the following topics:

Database documentation is a data catalog that holds detailed information about the database, its objects, relationships, data types, etc.

The documentation tool allows documenting SQL Server database objects, including a rich set of SQL object types, their properties, object dependencies, tables, views, etc. in HTML, PDF, and Markdown formats. With the tool, users can customize layout, create and structure content, modify documentation options with WYSIWYG-based templates, and format the generated documentation depending on a style template they choose. One can navigate through the database documentation tree in order to include or exclude objects in/from the documentation.

Documentation formats in dbForge Documenter - database schema documentation tool

The documentation tool can be used as a standalone tool or launched from the SQL Server Management Studio.

Objects that can be documented with the database documentation tool

With the tool, you can generate documentation for the following objects:

You can include into documentation one or multiple databases, their individual objects, and groups of objects. To include the objects, select the checkboxes next to the objects you want to document in the database documentation tree. To exclude the objects from the documentation, clear the checkboxes next to the corresponding objects in the database documentation tree.

Include or exclude database objects in the database documentation tree

Object type summaries

To view a summary of the object type, click the folder of this object type in the database documentation tree. For example, to view the summary of all the scalar-valued functions in the database, click the Scalar-Valued Functions folder in the database documentation tree.

View the summary of the database object in the database documentation tree

The number 11 next to Objects refers to the number of the selected objects of a particular object type. The name of the object is displayed as a link to the object. Clicking the link will navigate you to the object within the generated documentation. If you want to exclude the Depends On section from documentation, turn the On/Off toggle switch to the Off mode.

The Description column displays the text that was added to the database with the MS_Description extended property. When editing the description, the tool adds a new description to the database or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Note

Microsoft Azure SQL Database does not support MS_Description extended properties.

If properties are disabled for a particular object included in the group, the group switch will have the middle position. For example, we have disabled properties for the HumanResources.Department table. When switching to the group of tables, we see that the switch for the Properties section takes the middle position.

Group switch for the Tables folder in dbForge SQL documentation generator

Object sections and properties

All these sections are documented for each object in the documentation:

You can customize the documentation by including or excluding individual sections (such as Indexes, or SQL Script), properties (such as Filestream File Group, or Collation), objects, groups of objects, databases, or the entire SQL Server instance.

Description section

The Description section shows the text that was added to the database with the MS_Description extended property. You can edit the Description section. In this case, Documenter adds a new description to the database or updates the existing one. The description is not stored in the database documentation project file (.ddoc). If you want to exclude the description from documentation, turn off the toggle switch under Description. For example, the Description section for the HumanResources.Department table in the AdventureWorks2019 database will look in the editing mode as follows:

Edit the Description section for the table in the documentation

Properties section

The Properties section displays the particular properties of the documented object. Each type of object has its own set of properties to be displayed. You can exclude either the entire Properties section or a specific property from documentation by turning off the toggle switch. For example, the Properties section for the dbo.discount_price function in the AdventureWorks2019 database will look as follows:

View the Properties section for the function in the documentation

Permissions section

If any user was granted or denied privileges on database objects, the Permissions section displays information about the object permission as a table view.

The number next to Permissions refers to the number of permissions assigned to a particular object. To exclude the Permissions section, turn off the toggle switch.

View the Permissions section in the documentation

SQL Script section

The SQL Script section displays the DDL for the created object. To copy the DDL object to the clipboard, click Copy in the upper-right corner of the SQL Script section. To exclude the section from documentation, turn off the toggle switch. For example, the SQL Script section for the dbo.AccountNumber user-defined data type in the AdventureWorks2019 database will look as follows:

View the SQL Script section in the documentation

Extended Properties section

The Extended Properties section displays extended properties added to the objects other than the MS_Description extended property. The number refers to the number of dependent objects. To exclude the entire Extended Properties section, turn off the toggle switch.

View the Extended Properties section in the documentation

Depends On section

The Depends On section displays the objects on which the object to be documented depends. The number refers to the number of dependent objects. The name of the object is displayed as a link to the object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Depends On section from documentation, turn off the toggle switch. For example, the Depends On section for the Sales.Customer table in the AdventureWorks2019 database will look as follows:

View the Depends On section in the documentation

Used By section

The Used By section displays a list of database objects in which the object to be documented is used. The number represents the number of referring objects. The name of the object is displayed as a link to the object. If you click the link, you will navigate to the object within the generated documentation. If you want to exclude the Used By section from documentation, turn off the toggle switch. For example, the Used By section for the Sales.Customer table in the AdventureWorks2019 database will look as follows:

View the Used By section in the documentation

Object-specific sections

The section provides the description of the object-specific sections for the following objects:

Table

Columns section

The Columns section displays information about the properties of the columns as a table view. The number represents the number of columns in a particular table. To exclude the Columns section, turn the toggle switch to Off. For example, the Columns section for the Sales.ShoppingCartItem table in the AdventureWorks2019 database will look as follows:

View the Columns section for the table in the documentation

In the table, there are the following columns: Key, Name, Data Type, Length, Precision, Scale, Not Null, Identity, Rule, Default, Computed, Persisted, and Description.

Icons used in the Key column of the table documentation

Icons Description
Primary key icon in the table Displays a primary key for a column. When hovering over the icon, it displays the name of the primary key.
Unique clustered index icon in the table Displays a unique clustered index created for a given column. When hovering over the icon, it displays the name of the unique clustered index.
Check constraint icon in the table Displays a constraint of the Check Constraint type set for a given column. When hovering over the icon, it displays the name of the check constraint.
Foreign key icon in the table Displays a foreign key. When hovering over the icon, it displays the name of the foreign key.
Index icon in the table Displays that the column was indexed. When hovering over the icon, it displays the name of the index.
Index icon in the table The number indicates the number of indexes for the columns or notifies that the given column along with another column were indexed. When hovering over the icon, it displays the names of all indexes for a given column.

The Description column displays the text that was added to the database with the MS_Description extended property. You can edit the description and save the changes. After that, the tool adds a new description to the database or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Computed Columns section

If there are computed columns in the table, the Computed Columns section displays the table where you can view the formula for computed column values. The number represents the number of computed columns for a particular table. To exclude the Computed Columns section from documentation, turn the toggle switch to Off. For example, the Computed Columns section for the HumanResources.Employee table in the AdventureWorks2019 database will look as follows:

Computed Columns in the table

Indexes section

If there are indexes in the table, the Indexes section displays information about indexes in the table. The number represents the number of indexes created for a particular table. To exclude the Indexes section from documentation, turn the toggle switch to Off. For example, the Indexes section for the HumanResources.JobCandidate table in the AdventureWorks2019 database will look as follows:

Indexes in the table

Triggers section

If there are triggers in the table, they will be displayed as a table view under the Triggers section. The number represents the number of triggers created for a particular table. To exclude the Triggers section from documentation, turn the toggle switch to Off. For example, the Triggers section for the HumanResources.Employee table in the AdventureWorks2019 database will look as follows:

Triggers in the table

In the Description column, you can see the text that was added to the database with the MS_Description extended property. You can edit the description and save the changes. After that, the tool adds a new description to the database or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Check Constraints section

If there are constraints of the Check Constraint type in the table, they wiil be displayed as a table view under the Check Constraints section. The number represents the number of check constraints for a particular table. To exclude the Check Constraints section from documentation, turn the toggle switch to Off. For example, the Check Constraints section for the HumanResources.EmployeePayHistory table in the AdventureWorks2019 database will look as follows:

Check Constraints in the table

In the Description column, you can see the text that was added to the database with the MS_Description extended property. You can edit the description and save the changes. After that, the tool adds a new description to the database or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Foreign Keys section

If there are foreign keys in the table, they will be displayed as a table view under the Foreign Keys section. The number represents the number of foreign keys for a particular table. To exclude the Foreign Keys section from documentation, turn the toggle switch to Off. For example, the Foreign Keys section for the Person.PersonPhone table in the AdventureWorks2019 database will look as follows:

Foreign Keys in the table

In the Description column, you can see the text that was added to the database with the MS_Description extended property. You can edit the description and save the changes. The tool adds a new description to the database or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Statistics section

If user-defined statistics were collected on any of the table columns, the Statistics section displays statistics data in the table. The number represents the number of user-defined statistics collected for a particular table. To exclude the Statistics section from documentation, turn the toggle switch to Off.

Statistics in the table

Spatial Indexes section

If spatial indexes were created on table columns, the Spatial Indexes section displays information about spatial indexes in the table. The number represents the number of spatial indexes for a particular table. To exclude the Spatial Indexes section from documentation, turn the toggle switch to Off.

Spatial indexes in the table

View

Columns section

For columns of views, the Columns section displays information about column properties as a table view. The number represents the number of columns for a particular view. To exclude the Columns section from documentation, turn the toggle switch to Off. For example, the Columns section for the Person.vStateProvinceCountryRegion view in the AdventureWorks2019 database will look as follows:

Columns section for a view

In the Key field, Unique clustered index icon in the table informs that a unique clustered index was created for a given column. The Description field displays the text that was added to the database with the MS_Description extended property. You can edit the description and save the changes. In this case, the tool adds the new description or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Indexes section

The Indexes section displays information about the index properties. The number represents the number of indexes for a particular view. To exclude the Indexes section from documentation, turn the toggle switch to Off. For example, the Indexes section for the Person.vStateProvinceCountryRegion view in the AdventureWorks2019 database will look as follows:

Indexes section for a view

You can edit the description and save the changes. In this case, the tool adds the new description or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Statistics section

The Statistics section displays statistics data collected on object properties in the table. The number represents the number of object statistics in a particular table. To exclude the Statistics section from documentation, turn the toggle switch to Off.

Statistics in the table

Triggers section

The Triggers section displays information about triggers in the table. The number represents the number of triggers created for a particular view. To exclude the Triggers section from documentation, turn the toggle switch to Off.

Triggers in the table

Procedure

Parameters section

The Parameters section displays information about procedure parameters such as Name, Data Type, Data Length, and Description as a table view. The number represents the number of parameters for a particular procedure. To exclude the Parameters section from documentation, turn the toggle switch to Off. For example, the Parameters section for the dbo.uspGetWhereUsedProductID procedure in the AdventureWorks2019 database will look as follows:

Parameters in the table

You can edit the description and save the changes. In this case, the tool adds the new description or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Table-Valued Function

Parameters section

The Parameters section displays information about function parameters such as Name, Data Type, Data Length, and Description as a table view. The number represents the number of parameters for a particular table-valued function. To exclude the Parameters section from documentation, turn the toggle switch to Off. For example, the Parameters section for the dbo.ufnGetContactInformation table-valued function in the AdventureWorks2019 database will look as follows:

Parameters in the table

You can edit the description and save the changes. In this case, the tool adds the new description or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Scalar Function

Parameters section

The Parameters section shows information about input parameters of a scalar function and the returned result includes Name, Data Type, Data Length, and Description as a table. The number represents the number of parameters for a particular scalar function. To exclude the Parameters section from documentation, turn the toggle switch to Off. For example, the Parameters section for the dbo.ufnGetProductStandardCost scalar function in the AdventureWorks2019 database will look as follows:

Parameters in the table

You can edit the description and save the changes. In this case, the tool adds the new description or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Aggregate Function

Parameters section

The Parameters section shows information about input parameters of aggregate functions and the returned result includes Name, Data Type, Data Length, and Description as a table. The number represents the number of parameters for a particular aggregate function. To exclude the Parameters section from documentation, turn the toggle switch to Off.

Parameters in the table

You can edit the description and save the changes. In this case, the tool adds the new description or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Assembly

Method Signatures section

The Method Signatures section displays information about method signatures by connected assemblies (DLL files) and their physical files (name and size) in the script. To exclude the Method Signatures section from documentation, turn the toggle switch to Off.

Method Signatures in the table

If you want to copy a script to the clipboard, click Copy in the upper-right corner.

The Related Files section displays information about related files of assemblies in the table. To exclude the Related Files section from documentation, turn the toggle switch to Off.

Related Files in the table

User-Defined Table Type

Columns section

The Columns section displays information about column properties as a table view. The number represents the number of columns for a particular user-defined table. To exclude the Columns section from documentation, turn the toggle switch to Off.

Columns section for a user-defined table type

In the table, you can see the following information: Key, Name, Data Type, Length, Precision, Scale, Not Null, Identity, Default, Computed, Persisted, and Description.

Icons used in the Key column

Icons Description
Primary key icon in the table Displays a primary key for a column. When hovering over the icon, it shows the name of the primary key.
Unique clustered index icon in the table Displays a unique clustered index created for a given column. When hovering over the icon, it displays the name of the unique clustered index.
Check constraint icon in the table Displays a constraint of the Check Constraint type set for a given column. When hovering over the icon, it displays the name of the check constraint.
Index icon in the table Displays that the column was indexed. When hovering over the icon, it displays the name of the index.

The Description column displays the text that was added to the database with the MS_Description extended property. You can edit the description and save the changes. After that, the tool adds a new description to the database or updates the existing one. The description is not stored in the database documentation project file (.ddoc).

Computed Columns section

If there are computed columns in the user-defined table, the Computed Columns section displays the table where you can view the formula for computed column values. The number represents the number of computed columns for a particular user-defined table. To exclude the Computed Columns section from documentation, turn the toggle switch to Off.

Computed Columns in the user-defined table

Check Constraints section

If there are constraints of the Check Constraint type in the columns of user-defined table types, they will be displayed under the Check Constraints section. The number represents the number of check constraints for a particular user-defined table. To exclude the Check Constraints section from documentation, turn the toggle switch to Off. For example, the Check Constraints section for the HumanResources.EmployeePayHistory table in the AdventureWorks2019 database will look as follows:

Check Constraints in the table

XML schema colletion

Dependent Message Types

The Dependent Message Types section displays message types dependent on the Service Broker objects.

Dependent message types in the XML schema collection object

Contract

Message Usages section

The Message Usages section displays information about the usage of contracts in messages. The number represents the number of messages involved in a particular contract. The message name is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Message Usages section from documentation, turn the toggle switch to Off.

Message Usages in the table

Dependent Services section

The Dependent Services section displays information about dependent services. The number represents the number of the used services. The service name is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Dependent Services section from documentation, turn the toggle switch to Off.

Dependent Services in the table

Queue

Dependent Services section

The Dependent Services section displays information about dependent services. The number represents the number of the used services. The service name is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Dependent Services section from documentation, turn the toggle switch to Off.

Dependent Services in the table

Service

Contracts section

The Contracts section displays information about the contracts used for the Service object. The number represents the number of the used contracts. The contract name is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Contracts section from documentation, turn the toggle switch to Off.

Contracts in the table

Queues section

The Queues section displays information about queues. The number represents the number of the used queues. The queue name is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Queues section from documentation, turn the toggle switch to Off.

Queues in the table

Event Notifications section

The Event Notifications section displays information about event notifications. The number represents the number of the used event notifications. The name of the event notification is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Event Notifications section from documentation, turn the toggle switch to Off.

Event Notifications in the table

Event Notification

Event Types section

The Event Types section displays the name of an event type that causes the event notification to execute. The number represents the number of event types. To exclude the Event Types section from documentation, turn the toggle switch to Off.

Event Types in the table

Full-Text Catalog

Assigned Tables section

The Assigned Tables section displays information about assigned tables. The number represents the number of the used assigned tables. The name of the assigned table is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Assigned Tables section from documentation, turn the toggle switch to Off.

Assigned Tables in the table

User

Database-Level Permissions section

The Database Level Permissions section displays information about granted or denied database-level permission on the table. To exclude the Database Level Permissions section from documentation, turn the toggle switch to Off.

Database-Level Permissions in the table

Object-Level Permissions section

The Object Level Permissions section displays information about granted or denied object-level permission on the table. To exclude the Object Level Permissions section from documentation, turn the toggle switch to Off.

Object-Level Permissions in the table

Owned Schemas section

The Owned Schemas section displays schemas owned by a user. The number represents the number of owned schemas. The name of the owned schema is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Owned Schemas section from documentation, turn the toggle switch to Off.

Owned Schemas in the table

Database Role

Members section

The Members section displays information about granted database-level roles and permissions. The number represents the number of database-level roles. The name of the database-level role is displayed as a link to an object. Clicking the link will navigate you to the defined object within the generated documentation. To exclude the Members section from documentation, turn the toggle switch to Off.

Members section

A more detailed information on how to document database schema, please refer to the Generating documentation topic.

Download dbForge database schema documentation tool and try it absolutely free for 30 days!