xSQL Documenter Help


Introduction


xSQL Documenter is a database documentation tool that generates documentation in two formats: HTML or CHM. HTML is useful for publishing your documents on the web or an intranet site. CHM is useful if you want a single, compact file containing all your documentation about one or more databases. The CHM is indexed so it is easily searchable for any keyword. In addition of SQL Server xSQL Documenter supports a wide range of database management systems like Oracle, DB2, My SQL etc. - see the Supported Platforms section for a complete list.

Upgrading from trial to full version


The trial version of xSQL Documenter is fully functional but it prompts you to upgrade to the full version every time you initiate a documentation generation. In addition the trial version also includes "upgrade invitations" inside the documentation it generates.
To eliminate this mild annoyance you can acquire a license from this website. Once you have a license simply click on the "License key..." button at the bottom of the main xSQL Documenter screen and key in the name of the company/individual that the license is granted to and the license number.

Feedback and Support


You can submit any questions, comments and suggestions here... or alternatively you can email us at support@xsql.com

We provide unlimited, free email support to all users of our products so please feel free to contact us via email with any issues you may have. When submitting a bug report please provide as much information as possible including a script for generating the database during the documentation of which the error occurred.

What does xSQL Documenter do?


xSQLDocumenter is a tool for generating documentation for any database on the following platforms:
Given one or more connection strings, the tool will generate documentation about all the objects (tables, views, stored procedures, triggers, indexes, constraints, functions, etc) in your database. It will also tell you what the dependencies are (which tables point to other tables via foreign keys, which tables are used by which stored procedures, etc.) It will show the code for each trigger, view, user defined function, check constraint, and stored procedure in the database. In this way, it's easy to keep documentation in sync with the actual database, since you can just run the tool and the documentation gets updated.

If you provide more than one connection string, you can generate a single chm for multiple databases. This is useful if you have a large project involving many databases and you want a single searchable chm file for all of them.

xSQLDocumenter can also create data model diagrams containing any number of tables, views, stored procedures, user defined functions, etc. You specify what objects make up each diagram in a config file.

Other features include:
Certain features are only applicable to certain platforms however. For instance, there is no such thing as extended properties in a MySQL, Access, or Analysis Services database, so any feature related to extended properties would not be applicable on those platforms.

Requirements


Supported Platforms


xSQLDocumenter supports all major DBMS platforms. The sections below explain how to generate documentation for each of them using the command line interface (see Command line arguments section for a complete list of arguments). For more examples of connection strings to these various platforms, see http://www.connectionstrings.com.

Quick Start


This section gives you enough information to get started using xSQLDocumenter, but doesn't explain the more advanced features. Review the other sections of this document to get the full benefit of xSQL Documenter.
Tip: If you have a database with a large number of objects it can take a while for xSQLDocumenter to finish. When learning some of the more advanced features in xSQLDocumenter, such as data models and exclusion lists, it's useful to experiment on a small database first. That way if you go through a period of trial and error, you don't have to wait too long between trials.

Command line arguments


xSQLDocumenter is actually a console application that happens to launch a GUI if you invoke it with no arguments (that's why you always see a console pop up if you launch it from the Windows Shell). Use the /? Flag to get help about the available flags. Certain features are only available through the command line interface, and are not exposed in the GUI.

Because xSQLDocumenter is a console app, it's easy for DBAs to integrate xSQLDocumenter into their processes by calling it from .bat files, scheduled tasks, etc. For example, in SQL Server you could create an Agent job to invoke it every night and place the documentation on a web server, so that the documentation for your favorite databases is always up to date and available to anyone who needs it.

All command line flags are optional, with the exception that you must pass either a connection string or path to an xml file previously generated by xSQLDocumenter.

If you are using the GUI and want to see how you would invoke xSQLDocumenter from the command line with the settings you have specified in the GUI, you can click the “generate batch file” button and a .bat file will be created for you with the command line arguments you would pass. If you have selected objects to document using the “exclude objects…” button, a config file will be generated for you as well.

[/a 0|1] 1 = generate XML only, no documentation. Default is 0. That is, the default is to generate human readable, nicely formatted documentation.
[/A] Contains a comma-separated list of connection strings to MS Access databases. xSQLDocumenter supports Access 97/2000/XP/2003.
[/AX] Contains a comma-separated list of paths to xml files generated previously by xSQLDocumenter (for MS Access databases). Use this to generate docs for xml files that you generated previously using the /A flag. This feature is not available in the GUI.
[/b] Contains a comma-separated list of paths to XML files generated previously by xSQLDocumenter (for SQL Server 2000, 2005 and 2008 databases). Use this to generate docs for XML files that you generated previously using the /c flag. This feature is not available in the GUI.
[/B] Contains a comma-separated list of paths to xml files generated previously by xSQLDocumenter (for Analysis Server 2005 databases). Use this to generate docs for xml files that you generated previously using the /C flag. This feature is not available in the GUI.
[/c] Contains a colon-separated list of connection strings to SQL Server 2000, 2005 or 2008 databases.
[/C] Contains a colon-separated list of connection strings to Analysis Server 2005 databases.
[/CLC] Contains a path to clc.exe. This is necessary only if you are documenting Polyhedra databases and want to have the DDL for tables in the docs.
[/color 0|1] Specifies whether or not to colorize DDL in the documentation. 1 means color it, 0 means don't. Default is 0.
[/cf 0|1] Specifies whether or not to place comments about an object (such as a table column) next to the column name, or all the way to the right of the table. Default is 0 (to the right).
[/cs] Specifies the charset to use when creating the HTML files. Default is “utf-8” so that Unicode characters will display properly in the HTML. To display chars such as Å, Ä, Ö, ö, å, in the contents pane of the chm, use “iso-8859-1”. Depending on your system, other charsets will be available. You can see what charsets are available on your system by launching the GUI and scrolling through the charset dropdown.
[/css] Specify a path to a .css file that will be used to control the look and feel of the output documentation.
[/d] Contains a path to a XML config file, used for specifying data model diagrams or an exclusion list.
/delim Specifies an alternate delimiter to be used for separating connection strings. By default a colon is used. You would want to use this if you needed to use a colon in a single connection string, for example in a password. You can specify any single character for the delimiter.
[/D int] specifies an int to be used for datetime formats in the docs generated for SQL Server. Allowed values are here: http://msdn2.microsoft.com/en-us/library/ms187928.aspx. Default value is 100.
[/DB2] contains a colon-separated list of OLEDB connection strings to DB2 databases.
[/DB2S] Contains a colon-separated list of schema names to be documented, used only when documenting DB2 databases. If you use this flag, only objects in the schemas specified in the list will be documented.
[/DB2U] Contains a username passed to db2look.exe for use in generating DDL for tables, views, procedures, etc. Only used when /DB2 is passed. If you don’t pass /DB2U, then no DDL will show in the output documentation.
[/DB2P] Contains a password passed to db2look.exe for use in generating DDL for tables, views, procedures, etc. Only used when /DB2 is passed. If you don’t pass /DB2U, then no DDL will show in the output documentation.
[/DGPATH] Contains a path to ddlgen.bat. Only used if you want to see the DDL for tables for Sybase databases. Generally ddlgen.bat is located here: %SYBASE%\ASEP\bin\ddlgen.bat
[/DGUSER] Contains a username ser to pass to ddlgen.bat.
[/DGPASS] Contains a passowrd to pass to ddlgen.bat
[/DGSERVER] Contains a server/port pair to pass to ddlgen.bat, such as MYSERVER:5000
[/e] Contains a comma-separated list of paths to XML files describing what external objects you want to include in the documentation. See the External Objects section. This feature is not available in the GUI.
[/E 0|1] Specifies whether to include “edit” hyperlinks next to all extended property comments for objects in SQL Server database specs. This turns on the “edit extended properties” feature. Default is 0.
[/EP] Specifies a url to a page that handles the editing of extended properties. Only used if /E 1 is passed. Default value is “save_description.asp”.
[/h 0|1] Specifies whether to preserve formatting when displaying extended properties. 0 is the default. This feature is not available in the GUI.
[/hhc] Specifies the path to hhc.exe. Normally xSQLDocumenter will find this on its own, but if it can't for some reason, you can pass it on the command line: xSQLDocumenter seems to have trouble finding hhc.exe on 64 bit machines. Hhc.exe is used by xSQLDocumenter to create compiled help (chm) files.
[/hsn 0|1] Specifies whether to hide server names in the contents tree of the CHM or Javascript navigation tree. If 0, root nodes will appear as “SERVER.DatabaseName (Platform)”. If 1, they will appear as “DatabaseName (Platform)”. Default is 0.
[/IFX] Contains a colon-separated list of OLEDB connection strings to Informix databases.
[/IFXDBS] Contains the path to dbschema.exe on your system. This application is used to generate DDL for tables for Informix databases. If not passed, no DDL for tables will show up in the docs.
[/IFXCMD] Contains the path to a .cmd file that defines environment variables used by the Informix server. It is required for dbschema.exe to function properly. If not passed, DDL for tables is not shows in the docs.
[/i 0|1] Specifies whether to explicitly list out MS_Description and MS_DiagramPane extended properties in the Extended Properties section of the docs. Default is 0.
[/j] Specifies the maximum length in characters that you want to use for labels that appear in the reference and dependency graphs. Default is 24.
[/k] Specifies the number of icons per row that appear in the reference and dependency graphs. Default is 4.
[/ld path] Contains a path to a directory where a log file named xSQLDocumenter_progress.log will be written. If not passed, the value specified by /o is used. This feature is not available in the GUI. If you are generating documentation directly to a public location, this option is useful because the log file may contain information which you may not want to make public.
[/my] Contains a colon-separated list of connection strings to MySQL 5.0 or above databases.
[/myx] Contains a comma-separated list of paths to xml files generated previously by xSQLDocumenter (for MySQL databases). Use this to generate docs for xml files that you generated previously using the /my flag.
[/n] A name for your chm file. Default is the name of the database, or 'dbspec' if multiple values are passed in the /c or /b flags.
[/o path] Contains path to the output directory. Default is current directory.
[/O] Contains a colon-separated list of OLEDB connection strings to Oracle 9i or above schemas.
[/OS] Contains a colon-separated list of Oracle schema names to be documented, used only when documenting Oracle schemas. If you use this flag, you should pass the same number of values as connection strings passed in the /O flag. You can use /OS to document schemas other than the default schema of the user specified in the connection strings passed in the /O flag. See the examples below for how this is used.
[/OX] contains a comma-separated list of paths to xml files generated previously by xSQLDocumenter (for Oracle 9i or 10g databases). Use this to generate docs for xml files that you generated previously using the /O flag.
[/pt 0|1] Pass 1 to preserve HTML tags in extended properties/comments. You can use this if you have, for example, hyperlinks that you want rendered as links in your documentation. Note that if you pass 1 here, all your comments MUST be well-formed in the XML sense, otherwise you will see errors. Default is 0.
[/PG] Contains a colon-separated list of ODBC connection strings to PostgreSQL 8.0 or above databases.
[/PGDUMP] Specifies the path to pg_dump.exe, which is used to collect DDL for objects in a PostgreSQL database. Only used if /PG is passed.
[/POLY] Specifies a list of OLEDB connection strings to Polyhedra databases to document.
[/q] 1 = quiet mode. 0 is default. This feature is not available in the GUI.
[/r 0|1] 1 = draw pie charts to show table sizes, etc. 0 = don’t draw them. 1 is the default. If you pass 0, this information is shown in tabular form instead of a pie chart.
[/R int] Use this value to specify a number of sample rows from each table and view that you would like to show in the generated docs. That is, if you pass /R 10, then the top 10 rows from every table and view will appear in the docs. The default value is 0 (no sample rows).
[/sdp 0|1] sdp stands for "show database properties". If you pass 0, they will not be shown.
[/ssp 0|1] sdp stands for “show server properties”. If you pass 0, they will not be shown.
[/S int] Use this value to specify the maximum length of characters for sample row data. This arg is only used if /R is passed with a positive value. The default value is 100.
[/SY] Contains a colon-separated list of AseClient connection strings to Sybase ASE databases.
[/stat 0|1] 1 = generate documentation about table statistics (SQL Server only). Default is 0.
[/t int] Used to specify a timeout in seconds for the SQL queries sent to the servers in the connection strings passed in /c. 60 seconds is the default.
[/T 0|1] Used to specify if you want dependencies and pk/fk relationships in tabular form instead of a graph rendered in VML. Default is 0 (VML). Pass 1 for tabular.
[/threads int] Specifies the number of worker threads to use when transforming XML. If you have multiple processors on the machine where xSQLDocumenter runs, you can put them to use by specifying an value here larger than 1. Default is 1.
[/u 0|1] 1 = document SQL Server Agent Jobs, 0 = don’t. Default is 0.
[/VDB] Contains a list of connection strings to VistaDB databases to document. See the VistaDB section below for an example.
[/w 0|1] 1 = omit system objects, 0 = don't. Default is 1. This is useful if you want to document a database like msdb, where all the objects are shipped by Microsoft.
[/wn int] Specifies a positive integer for a number of hours. Any object that has changed between when you run xSQLDocumenter and this many hours ago will be shown on a “what’s new” page. It’s a good way to see what’s recently changed in your database. This feature is available for these platforms: SQL Server, Oracle, Access, DB2, MySQL, Sybase ASE, Sybase SQL Anywhere. Default value is 0 so the page won't show.
[/x 0|1] 1 = parse xml comments. Default is 0.
[/X 0|1] 1 = delete all output files except for the CHM and xSQLDocumenter_progress.log. Default is 0.
[/y 0|1] 1 = document object permissions. Default is 0.
[/?] Show help on the command line arguments.

Advanced features

Descriptive comments


This section applies to SQL Server only.

If you want descriptive comments for each database object to appear in your documentation, then these comments must first exist in the database as "extended properties". By default, if there is no extended property for a particular database object, then the default description that appears in the documentation is something like "none". To improve on this, you need to add an extended property to your object. To do so, use the MS provided stored procedure sp_addextendedproperty. You must name the extended property "MS_Description" for xSQLDocumenter to pick it up. Here's an example:

-- add an extended property to the authors table
USE pubs;
EXEC sp_addextendedproperty
'MS_Description' ,
'here is a comment about the authors table' ,
'user' , dbo,
'table' , authors

-- add an extended property to the address column in the authors table
EXEC sp_addextendedproperty
'MS_Description' ,
'here is a comment about the address column in the authors table' ,
'user', dbo,
'table', authors,
'column', address

-- add an extended property to the @percentage parameter
-- of the byroyalty stored proecedure
EXEC sp_addextendedproperty
'MS_Description',
'here is a comment about the @percentage param',
'user', dbo,
'procedure', byroyalty,
'parameter', '@percentage'

You can add comments to table and view columns, stored procedures, user defined functions, etc in this way. In the case of stored procedures, user defined functions, and views, you also have the option of using XML comments in the SQL code that will be picked up and parsed by xSQLDocumenter.

Editing Extended Properties


This section applies to SQL Server only.

It is also possible to edit extended properties on your SQL Server database objects directly from the documents generated by xSQLDocumenter. To do this, use the /E ad /EP command line arguments. If you pass /E 1, an “edit” link will appear next to each editable comment field for each object in the documentation. If you click on this link, you will see a text box where you can type your comment for the object, together with a cancel and submit button. If you hit submit, the following variables will be posted to a page (in the query string) that you specify in the /EP command line argument:
Name Description
server The SQL Server instance that the database resides on
database The name of the database. You can use the server and database params to construct a connection string to the database that contains the extended property in question.
text The value of the extended property
propName The name of the extended property (usually “MS_Description”)
level0type Value to be passed to sp_addextendedproperty in @level0type param
level0name Value to be passed to sp_addextendedproperty in @level0name param
level1type Value to be passed to sp_addextendedproperty in @level1type param
level1name Value to be passed to sp_addextendedproperty in @level1name param
level2type Value to be passed to sp_addextendedproperty in @level2type param
level2name Value to be passed to sp_addextendedproperty in @level2name param

Using these parameters, you can construct a connection string in an asp or aspx page (or similar technology, such as php) and execute a call to sp_addextendedproperty or sp_updateextendedproperty to update the appropriate property on your live server. In this way, your generated documentation becomes a powerful extended properties editor. Then, the next time you run xSQLDocumenter against your database, all your comments will appear in the generated docs.

Exclusion Lists


By default, xSQLDocumenter will generate documentation for all objects in your database that were not shipped by Microsoft – system stored procedures and the like are automatically excluded. To exclude other objects, you need to define an exclusion list.

If you are using the GUI, you can also exclude objects by clicking the “Objects…” button and choosing the objects that you want to document.

If you are working from the command line, or don’t want to have to click the “Objects…” button, then read on. The exclusion list is composed in XML and is part of the optional config file that xSQLDocumenter uses. Here’s an example:

<exclude>
  <server name="MYSERVER">
    <database name="MyDatabase">
      <!--
      For MYSERVER.MyDatabase, we will exclude
      sp_GetData, table_Employee, view_SomeStuff, and fn_DoWork.
      For sp_GetData, we will exclude from the docs the code only, and not
      other details about the sproc (such as its params, etc).
      
-->
      <object xtype="P" name="[dbo].[sp_GetData]" excludeCodeOnly="true" />
      <object xtype="U" name="[dbo].[table_Employee]"/>
      <object xtype="V" name="[dbo].[view_SomeStuff]"/>
      <object xtype="TF" name="[dbo].[fn_DoWork]"/>
    </database>
    <database name="SomeOtherDatabase">
      <!--
      For MYSERVER.SomeOtherDatabase, we will exclude
      sp_AddRowToTable and sp_CountRowsInTable.
      
-->
      <object xtype="P" name="[dbo].[sp_AddRowToTable]"/>
      <object xtype="P" name="[dbo].[sp_CountRowsInTable]"/>
    </database>
  </server>
  <server name="OTHERSERVER">
    <database name="OtherDatabase">
      <!--
      For OTHERSERVER.OtherDatabase, we will exclude sp_DoStuff.
      
-->
      <object xtype="P" name="[dbo].[sp_DoStuff]"/>
    </database>
  </server>
</exclude>


As shown in the example, you can exclude stored procedures, tables, views, and user defined functions from being documented. Some things to note:
Because the values in the name attributes are case sensitive, if you were trying to exclude “sp_getdata” from MYSERVER.MyDatabase and you used the sample above, it would not be excluded. You would have to change spelling in the sample above from “sp_GetData” to “sp_getdata”.

Global excludes

You can also exclude the code for entire classes of objects, or for all objects at once using a global value for the excludeCodeOnly attribute on the root <exclude> element. If you want to exclude all the DDL for all objects from your docs, your <exclude> element would look like this:

   <exclude excludeCodeOnly="true">

To selectively exclude the DDL for certain types of objects and not others (without having to specify the name of each as you would above), just specify the xtypes of the objects you want to exclude code for. For instance, this would exclude the DDL for all procs and functions:

   <exclude excludeCodeOnly="P,PC,X,FN,IF,TF,FS,AF,FT">

And this would exclude the DDL for all tables and views:

   <exclude excludeCodeOnly="U,V">

Each xtype in the attribute value should be separated by a comma, with no spaces. See the xtypes section below for a list of all the values you can put in the excludeCodeOnly attribute.

xtypes


The following table contains a list of object types and their xtypes that may be excluded from the docs. Many of the objects only exist on SQL 2005 servers (such as all the CLR objects).

Object xtype
Table U
View V
Sproc P
CLR sproc PC
Extended sproc X
Scalar udf FN
Tabled valued udf TF
Inline udf IF
CLR Aggregate udf AF
CLR scalar udf FS
CLR table valued udf FT
Rule R
Synonym SN
CLR trigger TA
Trigger TR
Schema SCHEMA
User defined type UDT
Certificate CERTIFICATE
Xml schema collection XML_SCHEMA_COLLECTION
Symmetric key SYMMETRIC_KEY
Asymmetric key ASYMMETRIC_KEY
Assembly ASSEMBLY
Package PACKAGE
Domain DOMAIN
Materialized view MATERIALIZED_VIEW


The last few don't have xtypes defined in SQL Server, so we made up our own – these are all the xtypes longer than two characters. Also, many of these only make sense for certain platforms. For instance, packages only exist in Oracle and DB2, and not any of the other platforms supported by xSQLDocumenter.

Data models


Data model diagrams are graphical representations of data that involve more than one database or external object. They are linked together via dependencies and primary/foreign key relationships. If you want to have such diagrams generated and placed in your documentation, you need to define a <models> section in your optional config file. Data model diagrams can include tables, views, stored procedures, user defined functions, as well as any external objects that you include in the documentation.

The idea is similar to the diagrams that you can make with Enterprise Manager in SQL Server 2000, except that you can include more than just tables in the diagrams, and you can include objects from different databases on different servers in the same diagram. Also, in Enterprise Manager, the links are made via primary/foreign key relationships only, whereas in xSQLDocumenter, dependencies are used (in addition to primary/foreign key relationships) to link objects together in the diagram.

xSQLDocumenter uses a statistical algorithm (simulated annealing) to try and find the optimal placement of the object icons such that the number of link crossings, the length of all the links, and (optionally) the number of overlapping links, is minimized. This makes the diagram more pleasing to the eye than just randomly placing the icons on the screen.

In the generated chm file, you will find all your data models in the left hand navigation tree (contents tab) collected under a node entitled "Data models".

When the htm files for your data model diagrams are generated, they are placed in a directory named "models". This directory is created in the output folder that you specify with the /o command line flag.

Here's an example of a <models> section for a set of documentation including both the pubs and northwind databases:

  <models>
  <model name="Pubs stuff" iconsPerRow="6" seed="1" allowOverlap="0" horizontalSpace="75" verticalSpace="75" maxLabelLength="12">
    <server name="MYSERVER" type="SqlServer">
      <database name="pubs">
        <object xtype="U" name="[dbo].[authors]"/>
        <object xtype="U" name="[dbo].[titleauthor]"/>
        <object xtype="U" name="[dbo].[publishers]"/>
        <object xtype="U" name="[dbo].[employee]"/>
        <object xtype="ComObject" name="Com Object 1"/>
        <object xtype="WebPage" name="Web Page 1"/>
      </database>
    </server>
  </model>
  <model name="Northwind and pubs" iconsPerRow="8" seed="2" allowOverlap="1" horizontalSpace="100" verticalSpace="80" maxLabelLength="16">
    <server name="MYSERVER" type="SqlServer">
      <database name="Northwind">
        <object xtype="U" name="[dbo].[Orders]"/>
        <object xtype="U" name="[dbo].[OrderDetails]"/>
        <object xtype="ComObject" name="Com Object 1"/>
        <object xtype="WebPage" name="Web Page 1"/>
      </database>
      <database name="pubs">
        <object xtype="U" name="[dbo].[authors]"/>
        <object xtype="U" name="[dbo].[titleauthor]"/>
        <object xtype="U" name="[dbo].[publishers]"/>
        <object xtype="U" name="[dbo].[employee]"/>
      </database>
    </server>
  </model>
  </models>


The structure of this XML is very similar to the exclusion list. Some things to note:

Since you probably don't want to type in all this xml by hand, xSQLDocumenter will create an xml file for you in the output directory called allobjects_datamodel.xml each time it runs. This is a sample config file with a single datamodel defined in it, with all objects in the database. You can just copy/paste from this file to create your own models, so you don't have to type. You can even pick and choose objects from different databases and different servers and combine them into a single model.

One final note: the time needed to place the icons increases rapidly (like n2) with the number of links connecting the icons. So be prepared to wait if you define a data model with 100 icons that are all linked together in a complicated way. It should take a few seconds for ~10 icons, about a minute for ~20, and several minutes for 50.

External Objects


Databases do not exist in a vacuum. Every database, if it is to be of any use to anyone, has a number of clients that depend on it. These clients take the form of external objects, such as COM objects, .NET assemblies, Java classes, Web Services, SQL scripts, ASP/ASP.NET/PHP/JSP pages, an executable file, or whatever you like. A web application utilizing .NET, for example, may consist of a SQL database, a couple web services, and several ASP.NET pages. The web services likely will make calls to the SQL database to get their work done. This means that the web services are dependent on various database objects. You may even have stored procedures in your database that make calls to external objects, making your stored procedures dependent on these external objects.

If you define your external objects in XML, then xSQLDocumenter will parse that XML and include your external objects in the documentation that it generates. In this way, you can generate a single chm that incorporates every object and dependency in your solution.

When the htm files for your external objects are generated, they are placed in a directory named "ExternalObjects". This directory is created in the output folder that you specify with the /o command line flag.

There are two parts to incorporating external objects into the documentation:
Let's look at the <definitions> part first:

  <definitions>
    <object xtype="WebPage" name="Web Page" plural="Web pages" color="#c0ffee"/>
    <object xtype="ComObject" name="Com Object" plural="Com Objects" color="#beeeef"/>
    <object xtype="Script" name="Script" plural="Scripts" color="#baaaad"/>
    <object xtype="Document" name="Document" plural="Documents" color="#f0000d"/>
  </definitions>

Important notes:
So in the example above, we have declared four types of external objects: one for COM objects, one for web pages, one for scripts, and one for documents. You can define as many or as few as you like, and name them however you like, subject to the restrictions above.

The second part of adding external objects to xSQL Documenter output is actually defining each external object you want represented. That is also done via XML. Object definitions must be in an XML files separate from the config file. It's analogous to .h and .cpp files in C++: you keep the declaration separate from the definition. Here's an example of an external object definition:

  <customObjects>

    <!--
    Here is where the definition of the object is.
    It’s just a XML element wrapping some HTML.
    You can put whatever you want in between the
    object tags, as long as it is well-formed.
    -->
    <object xtype="ComObject" name="Com object 1">
      <div>put some html here describing Com Object number 1...</div>
      <table><tr><td>some data</td></tr></table>
      <span class="code">
        put whatever you want between the "object" tags,
        as long as it's well-formed in the xml sense.
      </span>
    </object>

    <!--
    these are all the things that Com Object 1 depends on
    -->
    <dependency objName="[dbo].[employee]"
          xtype="U"
          database="pubs"
          server="MYSERVER"
          serverType="SqlServer"
          dependentObjectName="Com object 1"
          dependentObjectType="ComObject" />

    <dependency objName="Com object 2"
          xtype="ComObject"
          dependentObjectName="Com object 1"
          dependentObjectType="ComObject" />

    <dependency objName="Web Page 2"
          xtype="WebPage"
          dependentObjectName="Com object 1"
          dependentObjectType="ComObject" />

    <dependency objName="Web Page 1"
          xtype="WebPage"
          dependentObjectName="Com object 1"
          dependentObjectType="ComObject" />

    <!--
    here's a dependency that says that the reptq1 stored
    procedure in MYSERVER.pubs depends on Com Object 1.

    You only have to do this kind of thing if the
    dependent object is a database object (and not
    an external object), because the db has no way of
    knowing that this dependency exists otherwise. If
    another external object, QQQ, depends on Com Object 1,
    you would normally describe that dependency in the definition
    for QQQ, not here.
    -->

    <dependency objName="Com object 1"
          xtype="ComObject"
          dependentObjectName="[dbo].[reptq1]"
          dependentObjectType="P"
          database="pubs"
          server="MYSERVER"
          serverType="SqlServer" />
  </customObjects>

So, given these rules, we can decipher what is meant by the first <dependency> element in the list above. It says that Com Object 1, an external object of xtype "ComObject" depends on the "employee" table (because xtype="U") in the MYSERVER.pubs database, and the MYSERVER is a SQL Server.

Similarly, the second <dependency> element says that Com Object 1, an external object of xtype "ComObject" depends on "Com Object 2" external object, also of type "ComObject". The definition for this other external object, Com Object 2, is not shown.

The last <dependency> element in the example above says that the "reptq1" stored procedure in MYSERVER.pubs depends on Com Object 1.

Finally, each of the files you use for your external object definitions must be passed in a comma separated list using the /e command line flag.

If you follow this recipe for each of your external objects, then you can get them all into the chm produced by xSQLDocumenter. Admittedly, this would be a lot of work to do by hand, unless you only have a few external objects to worry about. The best thing would be to do it programmatically, perhaps using a tool like NDoc.

Custom navigation hierarchies


You can also have custom hierarchies represented in the navigation tree in the left hand window of the chm (the contents tab). You define that hierarchy in the config file, again via XML. Here's an example:

  < customContents exclusive="false">
    <item name="Scenario 1" href="">
      <item name="Web Page 1" href="ExternalObjects\WebPage_WebPage1.htm">
        <item name="Web Page 2" href="ExternalObjects\WebPage_WebPage2.htm">
          <item name="Com Object 1" href="ExternalObjects\ComObject_ComObject1.htm">
            <item name="authors table" href="SPRING.pubs\table_dboauthors.htm"/>
            <item name="titles table" href="SPRING.pubs\table_dbotitles.htm"/>
            <item name="employee table" href="SPRING.pubs\table_dboemployee.htm"/>
            <item name="Categories table" href="SPRING.northwind\table_dboCategories.htm"/>
          </item>
          <item name="Com Object 2" href="ExternalObjects\ComObject_ComObject2.htm">
            <item name="Categories table" href="SPRING.northwind\table_Categories.htm"/>
          </item>
        </item>
        <item name="Web Page 2" href="ExternalObjects\WebPage_WebPage2.htm">
          <item name="Web Page 1" href="ExternalObjects\WebPage_WebPage1.htm"/>
        </item>
      </item>
    </item>
  </customContents>


The idea here is that the hierarchy of the <item> elements is duplicated exactly in the navigation tree. Here's a breakdown:
It's also possible to completely exclude all automatically generated nodes from the table of contents in the chm except for those you explicitly specify. This is done by specifying exclusive="true" in the customComments element.

XML Comments


Many SQL developers put comments in their stored procedures, user defined functions, and views that describe things like usage, change history, parameter descriptions, etc. In many groups, adding such comments is a mandatory part of the process, so that they can keep track of why such-and-such stored procedure changes were made, etc. If such comments are wrapped in appropriate XML elements, xSQLDocumenter can parse them and place them in the documentation.

Here's an example of a stored procedure written with XML comments added:



   /*
      <summary>This is just a comment to give a summary of what the procedure is for.
      </summary>
      <scope>Public</scope>
      <historylog>
        <log revision="1.0" date="08/18/2003" bug="1234" email="jh">Created</log>
        <log revision="1.1" date="09/19/2003" bug="1234" email="jh">fixed bug 420247</log>
      </historylog>
      <returns>
        <return value="0" description="Success"/>
        <error value="1" code="E_FAIL" description="some generic failure"/>
        <error value="2" code="E_POINTER" description="some other failure"/>
        <recordset>
           <column name="ParentOrganizationName" datatype="nvarchar" datalength="20" description="dunno"/>
           <column name="IsPrinter" datatype="bit" description="is it a printer?"/>
        </recordset>
        <recordset>
           <column name="OSCode" datatype="int" description="some int"/>
        </recordset>
        <recordset>
           <column name="SiteCode" datatype="int" description="something else"/>
        </recordset>
        <recordset>
           <column name="LanguageCode" datatype="int" description="get the idea?"/>
        </recordset>
      </returns>
      <samples>
        <sample>
           <description>here is some sample code</description>
           <code>exec sp_SampleSproc @p1=1234, @p2=1.223, @p3=’blahblahblah’</code>
        </sample>
        <sample>
           <description>some more samples...</description>
           <code>exec sp_SampleSproc @p1=5678</code>
        </sample>
      </samples>

      here we can specify some dependencies of this object on other objects. This is only necessary
      to do if the dependency is not already in sysdepends or otherwise picked up by
      xSQLDocumenter. One example would be if you are using dynamic sql to select from a
      particular table in a proc. Another would be if you want to make a proc dependent on some
      external object (see the “external objects" section of the xSQLDocumenter help file for details).

      <dependency objName="[dbo].[MyTable01]" xtype="U" dependentObjectName="[dbo].[MyProc]" dependentObjectType="P" />
      <dependency objName="[dbo].[MyTable02]" xtype="U" dependentObjectName="[dbo].[MyProc]" dependentObjectType="P" />
   */

   CREATE Procedure [dbo].[sp_UselessSproc]
   --<parameters>
   @p1 int,     -- <param required="yes" description="this parameter is for x…"/>
   @p2 float = NULL,     -- <param required="no" description="this parameter is for y…"/>
   @p3 nvarchar(3000) = NULL     -- <param required="no" description="this parameter is for z…"/>
   --</parameters>
   AS
   BEGIN
   
   --<logic>First initialize variables</logic>
   DECLARE @myvar int

   --<logic>Next, print out a useless message</logic>
   PRINT ‘TODO: add some SQL code so something useful actually happens here’

   --<logic>Next, get all the data with no regard for the parameters we were passed!</logic>
   SELECT * from MyTable
   END

   GO



Each XML comment be commented out of the code in some way, either with "—" style comments or with "/* */" style comments. It should be pretty clear from the example how the comments work. With the exception of the <parameters> element, the XML comments may appear in any order and at any location in the stored procedure. The <parameters> element must wrap the parameters that the procedure uses.

The elements used are:

Element name Purpose
<scope> Says what the scope of the procedure is. Can anyone call it? Is it only used internally by other stored procedures? You can put any value you want inside this element, such as "public”, "private”, "protected”, "whatever”.
<summary> Contains a description of what the procedure is for.
<parameters> This element wraps the parameters that the procedure takes.
<param> This element describes each parameter: whether it is required or not, and a description.
<historylog> This element contains a history of changes to the procedure. Contains one <log> element for each change.
<log> Describes a change to the object. You can use any attributes you like to this element and they will show up in the generated docs.
<returns> Describes the return types of the procedure (both record sets and integer return values). Contains many <return> and <recordset> elements.
<return> Describes a particular return value of the procedure.
<recordset> Describes the record sets that a procedure might return.
<samples> Contains XML describing some sample code for how one might use this procedure.
<sample> Contains a particular usage scenario. Each contains a single <description> and a single <code> element.
<description> A description of some sample code.
<logic> This tag just contains a comment that will be placed into the output in a section entitled "Logic”. Each <logic> comment is placed in the docs in the order that they appear in the procedure, with formatting preserved.
<code> Some sample code for the procedure. Carriage returns are preserved.
<dependency> Used to specify dependencies of your object on other objects. It is only necessary if the dependency would not otherwise be known to xSQLDocumenter. For example, in SQL Server, if you have a proc that references a table via dynamic SQL then that dependency would not be in sysdepends and xSQLDocumenter would not discover it otherwise.
<object> For Oracle use only. This is used if you are adding xml comments to your package PL/SQL code, see below.


XML Comments in Oracle Packages

You can also put xml comments in your Oracle packages. In that case however, you need to wrap each procedure or function in <object> tags. Each <object> tag needs a xtype and name attribute. For packages that have wrapped bodies, you can put comments in the package specification instead. The priority is this:

Custom Comments


This feature applies to SQL Server only.

Using extended properties, you can add your own columns to the tables that list columns, indexes, constraints, and triggers (for tables and views) and parameters (for sprocs and udfs). For instance, let's say you had an extended property called Foo, and another called Bar, on each column of each table in your database. You could add these extended properties to the generated docs by adding a section to the config file like this:

  <customComments>
    <customComment order="1" type="COLUMN" name="Foo" displayName="foo"/>
    <customComment order="2" type="COLUMN" name="Bar"displayName="bar"/>
  </customComments>


The order attribute determines what property comes first in the columns table in the generated docs. All custom comments come after the "MS_Description" property.

Allowed values of the type attribute are: COLUMN, INDEX, CONSTRAINT, TRIGGER, and PARAMETER.

Here's an example of how to create custom comments for the columns of a table using the customComments feature, with two extra properties named Foo and Bar, as above:

  use pubs

  EXEC sp_addextendedproperty
  @name = N'Foo', @value = 'used in case of Foo.',
  @level0type = N'Schema', @level0name = dbo,
  @level1type = N'Table', @level1name = authors,
  @level2type = N'Column', @level2name = au_id;
  GO
  
  EXEC sp_addextendedproperty
  @name = N'Bar', @value = 'do not forget about Bar.',
  @level0type = N'Schema', @level0name = dbo,
  @level1type = N'Table', @level1name = authors,
  @level2type = N'Column', @level2name = au_id;

Sample config file


You can optionally run xSQLDocumenter with a config file, composed in XML. The config file is used for the following:
You must pass the path to the config file to xSQLDocumenter using the /d flag. You cannot specify a config file path using the GUI.

Here is a sample config file. Each section is explained in the sections above.

<SqlSpec>
  <!--
    in the models element, you specify what models you want to have in your chm.
    In each model element, you specify a list of database and/or external objects
    that you want in that model.
    -->
  <models>
    <model name="Pubs stuff"
        iconsPerRow="12"
        seed="1"
        allowOverlap="0"
        horizontalSpace="75"
        verticalSpace="75"
        maxLabelLength="12">
     <server name="MYSERVER" type="SqlServer">
      <database name="pubs">
       <object xtype="U" name="[dbo].[authors]"/>
       <object xtype="U" name="[dbo].[titleauthor]"/>
       <object xtype="U" name="[dbo].[publishers]"/>
       <object xtype="U" name="[dbo].[employee]"/>
      </database>
     </server>
    </model>
  </models>

  <!--
    In the definitions element, you define the types of external objects
    you will have, and what their properties are. The xtype attribute is
    the key that tells xSQLDocumenter what the object is. It is case
    sensitive, and is not allowed to have spaces in it. The name field
    is a human readable name, as is the plural. The color is the color
    that will be used when drawing that object type in a diagram.
    -->
  <definitions>
    <object xtype="WebPage" name="Web Page" plural="Web pages" color="#c0ffee"/>
    <object xtype="ComObject" name="Com Object" plural="Com Objects" color="#aaaaaa"/>
    <object xtype="Script" name="Script" plural="Scripts" color="#beeeef"/>
    <object xtype="Document" name="Document" plural="Documents" color="#a0a0a0"/>
  </definitions>

  <!--
    In the customContents element, you define what custom nodes you want
    to appear in the left hand contents navigation pane. Each item
    element must have a name and a href.The name is the name of the
    node as it will appear in the contents tree. The href is a path
    to a htm file that will be displayed when you click on the node.
    If you don't have a page for a particular node, just use an empty
    string for the href. The hierarchy of the item elements is the
    hierarchy of the nodes as they will appear in the contents
    navigation tree in the chm.

    The path to each htm in the href attribute may be absolute or
    relative. If relative, the path is relative to the output
    directory that you specify when xSQLDocumenter runs.
-->
  <customContents>
    <item name="Scenario 1" href="">
     <item name="Web Page 1" href="ExternalObjects\WebPage_WebPage1.htm">
      <item name="Web Page 2" href="ExternalObjects\WebPage_WebPage2.htm">
       <item name="Com Object 1" href="ExternalObjects\ComObject_ComObject1.htm">
        <item name="authors table" href="SPRING.pubs\table_dboauthors.htm"/>
        <item name="titles table" href="SPRING.pubs\table_dbotitles.htm"/>
        <item name="employee table" href="SPRING.pubs\table_dboemployee.htm"/>
        <item name="Categories table" href="SPRING.northwind\table_dboCategories.htm"/>
       </item>
       <item name="Com Object 2" href="ExternalObjects\ComObject_ComObject2.htm">
        <item name="Categories table" href="SPRING.northwind\table_Categories.htm"/>
       </item>
      </item>
      <item name="Web Page 2" href="ExternalObjects\WebPage_WebPage2.htm">
       <item name="Web Page 1" href="ExternalObjects\WebPage_WebPage1.htm"/>
      </item>
     </item>
    </item>
  </customContents>

  <!--
    In the exclude element, you declare what objects you want to exclude
    from the generated documentation. You must include the owner/schema
    prefix on the object name.
    -->
  <exclude>
    <server name="SPRING">
     <database name="pubs">
      <object xtype="P" name="[dbo].[reptq1]" excludeCodeOnly="true"/>
      <object xtype="P" name="[dbo].[reptq2]"/>
      <object xtype="P" name="[dbo].[reptq3]"/>
     </database>
    </server>
  </exclude>

  <header>
    <!--
    put any well-formed html in here you like.
    It will appear at the top of each page. Note the special strings
    #DatabaseName# and #PageName#. These will be replaced with
    appropriate values for each page.
    -->
    <div style="background-color:pink">
     <h1>Database documentation for: #DatabaseName#</h1>
     <h2>This page has information about: #PageName#</h2>
     <center>
      <span style="size:20pt">Powered by
      <a target="_blank" href="http://www.xsql.com">xSQL Software</a>
      </span>
     </center>
    </div>
  </header>

  <footer>
    <!--
    put any well-formed html in here you like.
    It will appear at the bottom of each page.
    -->
    <br/>     <center>
     <span style="size:20pt">Powered by
     <a target="_blank" href="http://www.xsql.com">xSQL Software</a>
     </span>
    </center>
  </footer>

  <!--
  the order attribute determines which one appears first in the applicable
  table in the docs. all custom comment fields appear after the MS_Description
  comment, after that the order is up to you.

  the type attribute says which object type this comment is for.

  allowed values for now are COLUMN, INDEX, CONSTRAINT, TRIGGER, PARAMETER (for sprocs/udfs)

the name attribute specifies the name of the extended property that will appear

  the displayName attribute specifies what text will be used as the column header
  -->
  <customComments>
    <customComment order="1" type="COLUMN" name="SourceProperty" displayName="source"/>
    <customComment order="2" type="COLUMN" name="MyOtherProperty" displayName="my other property"/>
    <customComment order="1" type="PARAMETER" name="SprocProperty" displayName="SprocProperty"/>
  </customComments>
</SqlSpec>

Branding


You can add your own arbitrary snippet of HTML as a header and/or footer of each generated page by using the branding feature. Just add a <header> and/or <footer> element to the config file with a well formed HTML snippet inside it, like this:

  <header>
    <!--
     put any well-formed html in here you like.
     It will appear at the top of each page. Note the special strings
     #DatabaseName# and #PageName#. These will be replaced with appropriate values for each page.
     -->
     <div style="background-color:pink">
       <h1>Database documentation for: #DatabaseName#</h1>
       <h2>This page has information about: #PageName#</h2>
       <center>
         <span style="size:20pt">Powered by
         <a target="_blank" href="http://www.xsql.com">xSQL Software</a></span>
       </center>
     </div>
  </header>

  <footer>
    <!--
     put any well-formed html in here you like.
     It will appear at the bottom of each page.
     -->
     <br/>
     <center>
       <span style="size:20pt">Powered by
       <a target="_blank" href="http://www.xsql.com">xSQL Software</a></span>
     </center>
  </footer>



You can use the branding feature to put your company's logo on each page, for example. In the header element, there are two special strings that will be replaced with values appropriate for each page. For instance, on the page for the authors table in pubs, #DatabaseName# would get replaced with "pubs" and #PageName# would get replaced with "[dbo].[authors]".

If you want to have a different header or footer for a particular object type, then put a type attribute on the <header> or <footer> element, such as <header type=”U”>. This would specify the custom header for pages describing the user tables in your documentation.