Generating SQL Server Documentation with NAnt and RedGate

The other day I posted a method by which you can automatically sync schema from one MS SQL Server db to another. While I was looking at the RedGate tools, I found a handy utility called SQL Doc. This doesnt have an api (and doesnt really need one) it consits of a GUI and a commandline client, which is good.

Basically, this app allows you to create a set of documentation on any given db including the setup scripts needed to replicate that particular db. It can output this report as a set of web pages, a .chm or a .doc. I thought that this was pretty cool, it represents a really good way of documenting the db at certain points through out its life. One good use of this tool would be to generate documentation prior to a new release (or just after). That way, you can maintain a handy record of the changes going into your db that can be published on your intranet/wiki etc.

Of course, as a GUI tool, this isnt so great for CI, so whacking the commandline parameters into a NAnt script will make it slot into your automated process nicely. Plus, it's an easy win - the task itself is very easy to write:

using System;
using System.Text;
using NAnt.Core;
using NAnt.Core.Tasks;
using NAnt.Core.Attributes;

namespace Custom.NantTasks.RedGate
{
    [TaskName("sqldoc")]
    class SQLDoc : ExternalProgramBase
    {
        private string _projectPath;
        [TaskAttribute("project", Required = true)]
        public string ProjectPath
        {
            get { return _projectPath; }
            set { _projectPath = value; }
        }
        private string _reportType;
        [TaskAttribute("report-type", Required = true)]
        public string ReportType
        {
            get { return _reportType; }
            set { _reportType = value; }
        }
        private string _password;
        [TaskAttribute("password", Required = true)]
        public string Password
        {
            get { return _password; }
            set { _password = value; }
        }
        private string _exePath;
        [TaskAttribute("app-path", Required = true)]
        public string ExePath
        {
            get { return _exePath; }
            set { _exePath = value; }
        }
        public override string ExeName
        {
            get
            {
                return _exePath;
            }
            set
            {
                ExeName = value;
            }
        }
      
        public override string ProgramArguments
        {
            get { return "/project:\"" + ProjectPath + "\"" + " /password:" + Password +" /filetype:" + ReportType; }
        }
        protected override void ExecuteTask()
        {
            Log(Level.Info, "Generating SQL documentation");
            Log(Level.Info, "Running the following command: " + ProgramArguments.ToString());
            base.ExecuteTask();
        }
    }
}

And thats it!! There are, however, a few caveats. In order to automate this, you first need to set up a project file through the GUI. Unfortunately, there doesnt seem to be a commandline route to do this. Once you have configured the project and saved it, you basically get an XML file - which is very handy as you could use NAnt to alter the values held within it (I havent done this). The build file would look a little something like this:

<target name="doc">
  <sqldoc project="C:\WidGetDev.sqldoc" password="password" report-type="html" app-path="C:\Program Files\Red Gate\SQL Doc 2\SQLDoc.exe" />
  </target>

Unfortunately, there is no way to specify a username on the commandline, only a password, which I guess is good enough. Once done, you can run this as a NAnt task pretty much anywhere. I did this on Vista, so I got some interesting security notifications pop up. I also got a very annoying bug following a sucessful build. For some reason, SQL Doc would fail to exit correctly (exiting on code 77, which means that the user does not have permission), however this just went away for me in the end... which was odd. Regardless, once the task has ran and completed, you will be able to pick up your documentation, SQL Doc places it in a location specified during the project creation, but you can change this whenever you like. After the task is run, just do what ever you fancy with the results, my idea was to post them to a wiki, but you should be able to attach them as a build artefact in which ever CI server you are using (if any).