How to document SQL databases automatically

Many DBAs think of database documentation as “grunt work”. It’s one of those tasks that you never have enough time for, as you’re always busy with other higher priority tasks.

But what if it could be made automatic?

Creating database documentation and keeping it up to date doesn’t have to be a burden. ApexSQL Doc can help with this.

ApexSQL Doc is a tool that documents SQL Servers databases, SSAS cubes, SSRS reports, SSIS packages, Tableau sites and SharePoint farms. It creates database documentation in a variety of formats, including compiled help (.CHM), HTML, PDF and MS Word. And best of all, the documentation process can be scheduled and run unattended:

  1. Start ApexSQL Doc

  2. On the Home tab, in the Projects group, click the New button to create a new project

  3. To add databases to documentation, on the Database engine tab, click the Add button

  4. In the Add SQL Server dialog, select a server and click the OK button:

    Add SQL Server dialog

  5. If there is a need to document the databases from multiple SQL Servers, repeat the steps 3 and 4
  6. Select the Server objects to document from the Server objects tab

  7. Add SQL Server dialog

  8. From the Database objects tab, select the object types and objects that will be documented

  9. If there are any objects that depend on the documented objects, they can be scripted by selecting Dependency (parent and children) lists/tables on the Database options tab. Including dependent objects improves the documentation accuracy

  10. From the Custom text tab, specify the header and footer text that will appear on each page

    Integration services tab

  11. When all options for documenting are set, click the Save button, from the Home tab, in the Projects group

    Integration services tab

  12. If the documentation is to be created immediately, click the Generate button from the Home tab

  13. To schedule documentation, create a batch file that will execute the created ApexSQL Doc project. Save the following command

    “C:Program Files\ApexSQL\ApexSQLDoc\ApexSQLDoc.com” /pr:D\:AdventureWorks2014.axdp /of:html /od:D:doc /v /on:{databasename}.{date}

    As the D:\doc\DocBatch.bat batch file

The {date} tag in the output file name will create a new name for your document each day, e.g. AdventureWorks.04_27_2016. If you don’t want to keep the older versions, use the /on:{databasename} file name template and add /f to overwrite the old file

The created file can be scheduled and executed from a batch, Task Scheduler, SQL Server Job and PowerShell project

Test the whole process, sit back and enjoy. The databases will be automatically documented in a form seen below:

SQL Database documentation

 

April 4, 2013