Automatic writing … it turns out that’s really a thing. But unlike the ghostly last chapters of Dickens’s unfinished last novel, there’s no paranormal stuff going on in the PowerShell automation script I wrote this week.
In our office we needed a better way to automatically produce up-to-date data definition documentation on one of our larger systems with 482 tables and 6775 columns, so we turned to a Redgate tool called SQL Doc. So far I really like the documentation output from this tool. It’s very comprehensive, easy on the eyes, and the resulting document is nicely hyperlinked.
To take full benefit of the documentation abilities in Redgate SQL Doc we’re in the process of adding all the MS_Description metadata properties which looks like this in SQL Server:
EXEC sp_addextendedproperty N'MS_Description', N'This is an important table', 'SCHEMA', N'dbo', 'TABLE', N'SystemInfo', NULL, NULL GO
At least two other competitors to Redgate take the same approach to manage the metadata for tables and columns in the MS_Description extended property directly in the actual SQL Server database.
So, other than adding the metadata, all we needed was a way to automate the process to produce up-to-date documentation since the database is still changing. The automation is pretty easy using the command line interface to sqldoc, explained here on the Redgate website.
As I often do, I chose PowerShell again to be the wrapper around this command line interface. There are numerous parameters and credentials that needed to be handled in a Settings.xml file. The first credentials are to the source database that’s being documented, the second credentials are to the destination file share. We needed these second credentials because we added a requirement to move the file to a different file share, (which may or may not already be mapped.)
Additionally, I needed to be aware of the impact of local credentials on the computer that hosts the Redgate software. One thing to be aware of is that if a different account is handling the automation for running the command line, then that account will also need to log-in to the GUI version of Redgate SQL Doc at least once. Another local system requirement was a missing registry key that I needed in order to schedule the command line within a job. Many thanks go out to Pete Ruiz at Redgate for quickly pointing my way to the missing registry key to help the job scheduler connect to the Redgate client service. And more kudos to Redgate for creating SQLDoc, a solid documentation tool. It’s no easy task to generate a 6000 page document from our database.
The PowerShell script is here on Github. As always, if the script doesn’t make sense leave an issue on the Github issues page here, or a comment below.