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 and SSIS packages. 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 Connect 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\ApexSQLDoc2016\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

  14. Start SQL Server Management Studio
  15. In the Object explorer expand the SQL Server Agent, right click on Jobs and select the New job from the list:

    Select the New job from the list

  16. In the General tab, specify the job name. In the Steps tab, click the New button to add a new step to the job, and specify an ”Operating system (CmdExec)” type for it
  17. Enter the following command:

    Cmd.exe /c “D:docDocBatch.bat”

    Job Step properties

  18. Open the Schedules tab and specify when the job should occur:

    New Job Schedule

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