How to document a SQL Server database with extended properties

ApexSQL Doc, a SQL Server documentation tool, displays descriptions for SQL database objects using the extended properties that are stored in the SQL Server database. The Extended property editor feature allows you to add or edit your database object descriptions using the extended properties that are stored in your SQL Server database.

The extended property of an object can be fully customized from a specialized dialog, opened by clicking the Extended property editor button in the Tools group of the Home tab:

Documenting SQL databases - Extended property editor

In the Connect to SQL Server dialog, you need to select the server and connection parameters, as well as the database you want to edit:

Connect to SQL Server dialog

After selecting the Extended property name from the drop-down menu, all object names that match the selected extended property name will be shown, and the Description box in the main grid will be filled. By default, “MS_Description” is selected as an extended property name.

You can use the filter row at the top of each column, to filter out unneeded objects, allowing you to quickly find the objects you want to edit:

Filtering out unneeded SQL objects in ApexSQL Doc

If you click the expand sign “+” next to the each object, you can further edit additional elements such as columns, primary keys, parameters etc:

Editing additional elements such as columns, primary keys

Adding or editing a description of a specific database object can be done by typing it directly into the description field in the grid, or using the Edit extended properties dialog, which is open by clicking the ellipsis button in the description field:

The ellipsis button in the description field

While the description field displays a very limited number of characters, in the Edit extended properties dialog, you can enter longer description, while retaining maximum visibility:

Edit extended properties dialog

SQL Server Management Studio makes managing objects’ extended properties, such as object descriptions, tedious work. The Extended property editor feature helps with the SQL database documentation, and simplifies the task making management and editing of extended properties easier.

Useful links:

Using Extended Properties on Database Objects
Viewing Extended Properties
Document Your SQL Server Databases with Extended Properties

March 4, 2015