How to Document Your SQL Server Database

The process of documenting a SQL Server database is a complete and continuous process that should start during the database design and development phases and continue during all database related life cycles in a way that ensures having an up-to-date version of the database documentation that reflects reality at any point in time. If performed properly, the generated database documentation file will contain an up to date and complete list for the database objects and a brief description for these database objects.

The SQL Server database documentation process can be performed using multiple ways. You can simply create a database diagram that shows a list of all database tables and columns and updates this diagram when any change is performed. But reading and maintaining such a diagram is not an easy process for large databases with dozens of database tables with each table contains dozens of columns.

Starting from SQL Server 2005, Microsoft introduced a new feature called the Extended Properties, that is stored in the database itself and accessed using sys.extended_properties system object, and return metadata information associated with the specified database or database objects. Documenting the SQL Server database using the extended properties is not the best choice, as you can document one database at a time, no historical data as the database objects properties will be deleted when this object is deleted, it is not a piece of cake process as it requires good development skills, requires big effort and consume long time.

Using Visual Studio

Developers who are familiar with Microsoft Visual Studio can easily take benefits from the SQL Server project type to connect to a SQL Server database and check the metadata about the database objects.

To achieve that, open the Visual Studio tool and create a new SQL Server Database Project from the New Project window, as below:

In the New Project window, provide a unique name for that project, where to save this project then click OK to create the SQL Server Database Project. When the project is created, click on the project properties and configure the Target Platform value with the SQL Server version of the target database, as shown below:

To connect to a specific database, right-click  the created project and choose Import -> Database option as follows:

From the Import Database window, select a connection from the previously saved connections list or provide the server name, authenticated credentials and the database name to connect to the database to be documented, as follows:

When you connect on the Connect button, the tool will start collecting metadata information about all database objects, as shown below:

After collecting and importing all database information, the selected database objects will be displayed in the solution explorer, categorized per schema, as follows:

To view metadata information about any database object, expand the schema from the solution explorer and click  that object and a new window will be opened showing all description for the selected object, with T-SQL script to create that specific object, as shown below:

It also provides you with the ability to show the Description column for each database object, by right-clicking on the free space beside the selected table and choosing the Description option. A new column will be displayed showing description for each column, with the ability to edit the description, as shown below:

Although it is too easy to document your database using Visual Studio, it does not provide a centralized place to check multiple database objects, provides information about single database per each project and cannot be exported to a user-friendly or printable format!

Using dbForge Documenter for SQL Server

To save your time and effort and have your database documentation up to date, it is better to use a 3rd party tool that makes the documentation process easier. dbForge Documenter for SQL Server is a database documentation tool that can be easily connected to your database and generates documentation of all SQL Server database objects in a few clicks.

dbForge Documenter for SQL Server provides us with a wide range of style templates and options that help in customizing the generated documentation in order to meet your own requirements. In few seconds of configuration, dbForge Documenter for SQL Server extracts all information and extensive details about the selected database, as well as inter-object dependencies and DDL T-SQL scripts to create these objects, with the ability to export the documentation in searchable HTML, PDF, and Markdown file formats. HTML format helps in publishing the database on the web, and PDF format is suitable for distributing to other systems and shared to other devices. dbForge Documenter for SQL Server can be also accessed to document the database directly via the SQL Server Management Studio as it is integrated with SSMS.

dbForge Documenter for SQL Server can be downloaded from the Devart download center and installed to your server by going through the straight-forward installation wizard, as below:

When you click on the Install button to start the installation process, you will be asked to specify the installation path for the tool, if you manage to create a desktop icon for the tool to access it faster, the versions of SQL Server Management Studio to have this tool as add-in on it, the files extensions that will be associated with the dbForge Documenter for SQL Server tool and finally you will be asked to specify the startup optimization mode for the tool. After that, the installation process will start, with a useful progress bar that shows what is being installed now, as shown below:

When the installation process completed successfully, the wizard will notify you and provide you with an option to launch the tool directly, as follows:

The first view of dbForge Documenter for SQL Server will be similar to the window below. To create documentation for your database using dbForge Documenter for SQL Server, click on the New Documentation window from the welcome page, as below:

In the opened documentation window, click on Add Connection to select an existing connection or adding a new connection, by providing the name of the server, valid credentials and the name of the database to connect to, using the friendly page below:

After connecting successfully to the database, dbForge Documenter for SQL Server will list all databases and database objects under the connected SQL Server instance. In the beginning, it provides you with an option to provide a unique name and description for the documentation to be generated, in addition to your own logo, name, and date to be displayed in that documentation, as shown below:

To document a specific database or database objects, check the name of the database from the databases list, review and tune the different database properties and options to be included in the documentation, but turning on or off the include button beside each property and option, as shown below:

After customizing what to include in your documentation, click on the Generate option to generate database documentation, based on your selections, as follows:

In the Generate Documentation window, specify the format of the generated documentation and the path and characteristic of the generated file name, as shown below:

If you click the Generate button, the documentation generation process will start, with a user-friendly checklist and progress bar to show the current status of the generation process, as below:

When the documentation generation process completed successfully, dbForge Documenter for SQL Server will notify you with the final result, as below:

Browsing to the path where the file is saved, you will see that the database documentation is generated under that path in PDF format, as shown below:

The report will be opened also in the dbForge Documenter for SQL Server tool, showing description for the database, list of all database objects and files and the properties and options for the selected database, as shown below:

dbForge Documenter for SQL Server provides you also with the ability to dive deeply on each database object. For example, click on the Tables hyperlink, choose the table you are interested in and full information about the selected table will be displayed in the report, as shown below:

It is clear from the example below, how we can use the dbForge Documenter for SQL Server 3rd party tool in few clicks to generate customizable documentation for your databases, that can be used for multiple purposes. Go and try documenting your database using dbForge Documenter for SQL Server!