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:
  • SQL Server 2000/2005/2008
  • Oracle 9i and above
  • DB2 8.2 and above*
  • Sybase 12.0.0.4 and above
  • Sybase SQL Anywhere 10.0 and above
  • PostgreSQL 8.0 and above
  • MySQL 5.0 and above
  • Analysis Server 2005/2008
  • Access 97 and above
  • Informix IDS 10 and above
  • VistaDB 3.0 and above
  • ENEA Polyhedra 7.0 and above
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:
  • Extending the documentation to cover external objects, such at Java packages, COM, .NET assemblies, Web Services, scripts, etc. You must specify your external objects and how they depend on the internal database objects in separate XML files.
  • Specify an exclusion list to exclude certain objects you specify from appearing in the documentation. This is useful if there are certain stored procedures that you don't want to expose to the consumers of the documentation. You might not want everyone reading the code for sp_GiveEmployeeBigFatRaise, for example.
  • Custom navigation hierarchies in the chm navigation tree.
  • Collecting various properties of the server the database resides on.
  • Documents all SQL jobs on the server, if the user has permissions to execute msdb..sp_help_job and its relatives.
  • Shows a call tree for sprocs and udfs that call other sprocs and udfs.
  • Documents SQL Server 2005 objects such as Assemblies, Symmetric and Asymmetric Keys, Certificates, XML Schema Collections, and the like.
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


  • The .NET framework (version 2.0) must be installed on the machine where you run the xSQLDocumenter.exe. Get it here if you don’t have it: http://msdn2.microsoft.com/en-us/netframework/aa731542.aspx.
  • To generate specs for Analysis Services specs, you must install Analysis Management Objects (AMO). AMO is the managed code library that wraps all of AS 2005 (it’s used heavily by SSMS, so if you have the SQL Server 2005 client tools installed, you already have it). You can get AMO here if you don’t have it: http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en (search for “AMO” on that page).
  • You do not need to have the .NET framework installed on the server where your database resides.
  • If you want to compile the output into chm files, you need the Microsoft HTML Help Workshop installed. You may already have it installed. xSQLDocumenter will detect if you have it installed or not. If you don't have it, it will ask you if you want to install it. If you say yes, it will download and install it. Alternatively you can get it yourself from here: http://go.microsoft.com/fwlink/?LinkId=14188.
  • To generate documentation for databases on MySQL 5.0 and above, you must have MySQL Connector/.NET installed on the computer where xSQLDocumenter runs. You can get it here: http://dev.mysql.com/downloads/connector/net. After you install it, you need to copy MySql.Data.dll to the directory where xSQLDocumenter.exe is located.
  • To generate documentation for Oracle schemas, you need to have an Oracle OLE DB driver installed on the computer where xSQLDocumenter runs. You can download a driver from Oracle here: http://www.oracle.com/technology/software/tech/windows/ole_db/index.html.
  • To generate documentation for Sybase ASE databases, you need to install AseClient, a .NET data provider for Sybase ASE. This component is available in the Sybase ASE SDK, and also ships with ASE itself: https://sybase.subscribenet.com.

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.
  • SQL Server 2000/2005/2008

    Example1
    xSQLDocumenter.exe /c "server=myserver; database=pubs; user id=myuser; pwd=mypass" /o "c:\my db specs" /x 1 /t 120 /q 1

    In this example, the output would be placed in c:\my db specs\MYSERVER.pubs, parsing of XML comments is turned on, timeout is set at 120 sec, and quiet mode is turned on.

    Example2
    xSQLDocumenter.exe /c "server=myserver;database=mydb;user id=myuser;pwd=mypass:server=(local);database=pubs;Trusted_Connection=yes;" /n MyDocs

    In this example, documentation will be generated from two databases and placed into a single MyDocs.chm file.

    Example3
    xSQLDocumenter.exe /b "c:\my db files\pubs.xml,c:\my db files\northwind.xml" /o "c:\my db files\output" /n NorthwindAndPubs /r 0

    In this example, pubs.xml and northwind.xml will be parsed and a chm named NorthwindAndPubs.chm will be produced and placed in c:\my db files\output. No pie charts for table sizes will be drawn. No queries to any SQL servers are performed.

  • Analysis Server 2005/2008

    You can document any Analysis Server 2005/2008 database using xSQLDocumenter. To do it, use the /C command line argument. For example:

    xSQLDocumenter.exe /C "Data Source=SUMMER; Initial Catalog=AmoAdventureWorks" /o c:\analysis_server_specs

    You can also combine the spec for a SQL Server database and a Analysis Server database into a single chm:

    xSQLDocumenter.exe /C "Data Source=SUMMER; Initial Catalog=AmoAdventureWorks" /c server=SUMMER;database=AdventureWorks;trusted_connection=yes; /o c:\analysis_server_specs

  • MySQL

    You can document any MySQL 5.0 and above database using xSQLDocumenter. To do it, use the /my command line argument. xSQLDocumenter uses MySQL Connector/NET for connecting to MySQL, which you can download here: http://dev.mysql.com/downloads/connector/net. Note that MySql.Data.dll must reside in the same directory as xSQLDocumenter.exe. MySql.Data.dll is the assembly that implements the MySQL Connector/NET library. Once you install Connector/NET, just copy MySql.Data.dll to where xSQLDocumenter.exe resides.

    Example: xSQLDocumenter.exe /my "server=localhost; database=sakila; uid=someuser; pwd=somepass" /o c:\mysql_specs

  • Oracle

    You can document any Oracle 9i and above schema using xSQLDocumenter. To do it, use the /O command line arg, passing an OLEDB connection string. All objects in the schema for the user that you connect as will be documented. For example:

    xSQLDocumenter.exe /O "Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=OE;Password=OE" /o c:\oracle_specs

    By default when documenting Oracle databases, only the objects in the default schema of the user specified in the connection string are documented. If you want to objects in a schema other than the default schema of a given user, you can use the /OS flag. In the following example, all objects in the SYS and SYSTEM schemas will be documented. In the case of SYS, the credentials of PowerUser1 are used, and for SYSTEM, the credentials of PowerUser2 are used.

    xSQLDocumenter.exe /O "Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=PowerUser1;Password=asdf1234:Provider=OraOLEDB.Oracle;Data Source=ORCL;User Id=PowerUser2;Password=qwerty7890" /OS SYS:SYSTEM /o c:\oracle_specs /n sys_and_system

  • DB2

    You can use xSQLDocumenter to document any DB2 database. It has been thoroughly tested on DB2 8.2, but may work on earlier versions as well.

    To document a DB2 database, use the /DB2, /DB2U, and /DB2P command line arguments, described in the command line arguments section. xSQLDocumenter uses OLEDB to connect to DB2, so you must specify an OLEDB connection string in the /DB2 command line argument.

    For example:

    xSQLDocumenter.exe /DB2 "Provider=IBMDADB2;Database=SAMPLE;Hostname= SUMMER;Protocol=TCPIP;Port=50000;Uid=username;Pwd=password" /DB2U username /DB2P password

    Of course you can generate docs for databases in other DBMS in the same chm by using other switches. This command line would generate a DB2 and SQL Server spec in the same chm, with 20 sample rows from each table and view included in the docs:

    xSQLDocumenter.exe /c "server=SUMMER;database=AdventureWorks;trusted_connection=yes;" /DB2 "Provider=IBMDADB2;Database=SAMPLE;Hostname=SUMMER;Protocol=TCPIP;Port=50000; Uid=username;Pwd=password" /DB2U username /DB2P password /R 20

  • PostgreSQL

    You can document any PostgreSQL 8.0 and above database using xSQLDocumenter. To do it, use the /PG command line argument. xSQLDocumenter uses ODBC for connecting to PostgreSQL, so you need to install an ODBC driver for PostgreSQL on the client machine where xSQLDocumenter runs. You should already have one installed if you have the PostgreSQL client tools installed.

    Below is a sample of how to document a PostgreSQL database. The /PG flag contains a colon-separated list of connection strings, and the /PGDUMP flag specifies the path to pg_dump.exe, which is used to extract DDL for the objects in your database. If you don’t pass /PGDUMP, no DDL will appear in your docs.

    xSQLDocumenter.exe /PG "Driver={PostgreSQL UNICODE};Server=localhost;Port=5432; Database=dellstore;Uid=postgres;Pwd=password;" /PGDUMP "C:\Program Files\PostgreSQL\8.2\bin\pg_dump.exe" /o c:\postgresql_specs

  • Sybase ASE

    You can document a Sybase ASE 12.0.0.4 and above database using xSQLDocumenter. To do it, use the /SY command line argument. xSQLDocumenter uses Sybase.Data.AseClient.dll for connecting to Sybase ASE, so you need to install it on the client machine where xSQLDocumenter runs. You should already have it installed if you have the Sybase client tools installed. If you don't have it installed, you may need to run the Sybase ASE installer again.

    Below is a sample of how to document a Sybase ASE database. The /SY flag contains a colon-separated list of connection strings, and the /DGPATH flag specifies the path to ddlgen.bat, which is used to extract DDL for the objects in your database. If you don't pass /DGPATH, no DDL for tables will appear in your docs. If you pass /DGPATH, you must also pass /DGUSER, /DGPASS, and /DGSERVER. These are the other arguments that are passed to ddlgen.bat which specify a user, password, and server to connect to.

    xSQLDocumenter.exe /SY "Data Source=SUMMER;Port=5000;Database=pubs3;Uid=sa;Pwd=" /DGPATH "C:\sybase\ASEP\bin\ddlgen.bat" /DGUSER sa /DGPASS abcd?1234! /DGSERVER summer:5000 /o c:\sybase_specs

    You may initially have some trouble getting xSQLDocumenter to connect to your Sybase ASE server using AseClient. If so, try this:
    • First, make sure you have a copy of Sybase.Data.AseClient.dll on your computer. Often it will be located here: %SYBASE%\DataAccess\ADONET\dll. You should also find sybdrvado115.dll, msvcp71.dll and msvcr71.dll in that same directory.
    • Copy sybdrvado115.dll, msvcp71.dll and msvcr71.dll to the C:\WINDOWS\system32 directory. You can find them in the same directory as Sybase.Data.AseClient.dll.
    • Copy Sybase.Data.AseClient.dll into the directory where xSQLDocumenter.exe is located.
    After following these steps, xSQLDocumenter should be able to document Sybase ASE databases.

  • Sybase SQL Anywhere

    You can document a Sybase SQL Anywhere 10.0 and above database using xSQLDocumenter. To do it, use the /SA command line argument, or enter an ODBC connection string to a SQL Anywhere database in the GUI. If using the command line, you must pass an ODBC connection string in the /SA argument. Example:

    xSQLDocumenter.exe /SA "Uid=dba;Pwd=MyPassword;Dsn=SQL Anywhere 10 Demo;" /o c:\sql_anywhere_specs

  • Informix

    You can document any Informix 9.4 and above database using xSQLDocumenter. To do it, use the /IFX command line argument.

    Below is a sample of how to document an Informix database. The /IFX flag contains a colon-separated list of connection strings. The /IFXDBS flag specifies the path to dbschema.exe on your system – it is used to generate DDL for the tables in your database. The /IFXCMD flag specifies the path to a batch file that defines various environment variables such as %INFORMIXSERVER%, etc. Without these environment variables being defined, dbschema.exe will not work properly. If you don't pass /IFXDBS or /IFXCMD, DDL for tables will not appear in your docs.

    xSQLDocumenter.exe /IFX "Provider=Ifxoledbc.2;Password=Zebra!01;User ID=informix; Data Source=stores_demo@summer; Persist Security Info=true;" /CLC "C:\Program Files\IBM\Informix\summer.cmd" /IFXDBS "C:\Program Files\IBM\Informix\bin\dbschema.exe" /o c:\informix_specs

  • MS Access

    To generate documentation for MS Access databases from the command line, specify an OLEDB connection string to each mdb file. If documenting more than one mdb, separate the connection strings by commas:

    xSQLDocumenter.exe /o c:\access_specs /A "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\msaccess\AccWebFAQ.mdb,Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\msaccess\Sample.mdb"

  • VistaDB

    You can document VistaDB 3.0 databases using xSQLDocumenter. To do it, use the /VDB command line argument. VistaDB is an in-process database engine implemented in managed code, available here: www.vistadb.net.

    Below is a sample of how to document a VistaDB database. The /VDB flag contains a list of VistaDBConnection connection strings, separated by the chart you specify in the /delim argument. If /delim is not passed, a comma is assumed.

    Finally, xSQLDocumenter requires that a copy of VistaDB.NET20.dll is located in the same directory as xSQLDocumenter.exe. This DLL implements the VistaDB engine and is located here in a default install of VistaDB: C:\Program Files\VistaDB 3.0\Frameworks\NET 2.0\Runtime.

    xSQLDocumenter.exe /VDB "Data Source=C:\Program Files\VistaDB 3.0\Data\DBDemos.vdb3; Open Mode=SharedReadOnly" /o c:\vistadb_specs

  • Polyhedra

    Starting with xSQLDocumenter 3.7, you can document Polyhedra databases using xSQLDocumenter. To do it, use the /POLY command line argument. Polyhedra is an in-memory database for high-availability systems made by Enea Software.

    Below is a sample of how to document a Polyhedra database. The /POLY flag contains a list of OLEDB connection strings. The /CLC flag specifies the path to clc.exe on your system – it is used to generate DDL for the tables in your database. If you don't pass /CLC, DDL for tables will not appear in your docs.

    xSQLDocumenter.exe /POLY "Provider=PatOleDB.PatOleDB.1;Data Source=localhost:8001" /CLC "C:\Polyhedra\poly7.0\win32\i386\bin\clc.exe" /o c:\poly_specs

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.
  • Click on xSQLDocumenter.exe to launch the GUI. You can also run it from the command line passing command line arguments, see the command line arguments section.
  • Enter the connection string(s) to the database(s) you want to document.
  • Hit the Go! Button. As it does its work, it will print out progress. You can cancel while it's running by hitting the Cancel button.
  • When it's finished, click on the “View .chm” button to see your documentation. By default the output is placed in a folder called “output” in the same directory as xSQLDocumenter.exe.
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:
  • Under the <exclude> element, you can have any number of <server> elements.
  • Under the <server> elements, you can have any number of <database> elements.
  • Under the <database> elements, you can have any number of <object> elements each needs an xtype and name attribute to say what the type of object is, and what its name is.
  • For objects that have related code, such as views, sprocs, and udfs, you can use the excludeCodeOnly=”true” attribute to say you want to keep the object in the documentation, but don’t include the code for it.
  • On all elements, the “name” attribute is required.
  • The values of the “name” attributes on all elements are case sensitive.
  • The <exclude> element must appear as a child of the root <xSQLDocumenter> element in the config file.
  • If you want to exclude the code for ALL objects in the spec, put excludeCodeOnly=”true” as an attribute on the top level <exclude> element.
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:

  • Under the <models> element, you can have any number of <model> elements.
  • Each <model> element describes a particular data model diagram:
    • The "name" attribute is required and specifies the name of the data model.
    • The "iconsPerRow" attribute is optional and specifies the maximum number of icons (there is one icon for each object) will fit on a single row. In the diagram, icons are placed on a grid, so this value says how many icons could appear in a single row if all the positions in the row were occupied. If you don’t specify it, the default value is 8.
    • The "seed" attribute is an integer that is used to seed a random number generator that is used in the icon placement algorithm that xSQLDocumenter uses. The default value is 1. If you pass a different value, the positions of the icons will change. If a particular placement doesn't appeal to you, try changing the seed and see how things change.
    • The "allowOverlap" attribute specifies if you want to allow the lines in the diagram that represent links between objects to overlap or not. A value of 1 means allow, 0 means don't allow overlap. The default is 0.
    • The "horizontalSpace" attribute specifies how much space you want to leave between icons in the horizontal direction. Default is 80.
    • The "verticalSpace" attribute specifies how much space you want to leave between icons in the vertical direction. Default is 80.
    • The "maxLabelLength" attribute specifies how many characters of an icon's name you want to show as its label. The default value is 8. That means that if your objects name is "MyLongNamedObject" it would appear as "MyLongNa…" if you leave the default. However, when you hover over the icon with the mouse, you will see the full name of the object.
  • Under each <model> element, you may have any number of <server> elements. This means you can have data model diagrams that incorporate database objects from different databases on different servers.
  • Under each <server> element, you may have any number of <database> elements.
  • Under each <database> element, you can have any number of <object> elements. The <object> element is used to specify any database object together with the xtype attribute, and also any external objects.
  • The "name" attribute is required on all elements except for <models>
  • The "xtype" attribute is required on <object> elements.
  • The value of the "name" attribute is case sensitive in all cases.
  • The <models> element must appear as a child of the root <xSQLDocumenter> element in the config file.
  • The "type" attribute is required on the server element. Allowed values are:
    • SqlServer
    • AnalysisServer
    • Oracle
    • MySQL
    • PostgreSQL
    • DB2
    • Sybase
    • Access
    • Informix
    • VistaDB
    • Polyhedra
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:
  • First, you must declare each type of external object that you want to represent in the <definitions> element of the config file.
  • Second, for each external object that you want to include, you must compose some html that you want to be displayed for that object, as well as define the dependencies of that object on all other objects.
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:
  • Under the <definitions> element, you may have any number of <object> elements.
  • Each <object> element declares a type of external object.
    • The type of object is specified by the value of the "xtype" attribute. The value may be anything you want, except that they must all be different (case-insensitive), and that you can't use any that are already listed in the xtypes section.
    • If you use the xtype="MyType" then you cannot use xtype="mytype" for a different object because the xtype is used for naming htm files that xSQLDocumenter produces, and file names are case-insensitive in Windows! You wouldn"t want to do that anyway, because that would be confusing. So don't do it.
    • The "name" attribute is a human readable name for your external object type. It can be whatever you want.
    • The "plural" attribute is a human readable name for the plural form of whatever you put for the "name" attribute. Usually you just add an "s".
    • The "color" attribute is used to specify a color for icons that appear in diagrams (dependency graphs and data model diagrams). It is a hex RGB value, and needs a hash sign on the front, as in the examples above.
  • The <definitions> element must appear as a child of the root <SqlSpec> element in the config file.
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>

    To note:
  • The root element of an external object is <customObjects>
  • Under <customObjects>, you may have any number of <object> elements.
    • Each <object> element must have an "xtype" attribute and a "name" attribute, both values case sensitive.
    • The value of the "xtype" attribute must match one of the xtypes in the <definitions> element in your config file.
    • The value of the "name" element can be anything you want.
    • The child elements of <object> make up some html, whatever you want. This will be used to make an html page describing the external object in the documentation.
  • Under <customObjects>, you may have any number of <dependency> elements. Each one describes a dependency that your object has on some other object, or vice versa. This other object could be another external object, or it could be a database object.
    • The "objName" attribute contains the name of an object.
    • The "xtype" attribute contains the xtype of the object referred to by the "objName" attribute.
    • if xtype and objName identify an object in a database, an not an external object, you must further specify these attributes:
      • The "database" attribute specifies the name of the database that the object identified by objName lives in.
      • The "server" attribute specifies the name of the server that the database identified by the "database" attribute.
      • the "serverType" says what type of server it is. It can have these values:
        • SqlServer
        • Oracle
        • DB2
        • Sybase
        • MySQL
        • PostgreSQL
        • Access
        • AnalysisServer
    • The "dependentObjectName" attribute contains the name of an object that depends on the object referred to by "objName".
    • The "dependentObjectType" attribute contains the xtype of the object referred to by "dependentObjectName".
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:
  • Under <customContents>, you may have any number of <item> elements. Each of these <item> elements will become a root node in the navigation tree in the chm file.
  • The "exclusive" attribute on the customContents node specifies whether or not to suppress the built-in table of contents produced by xSQLDocumenter. If you specify "true" in this attribute, the only nodes that will appear in your table of contents will be those items specified in the customContents block.
  • Under any <item> element, you may have any number of <item> elements, and so on, forever, as deep as you like.
    • Each <item> element must have a "name" attribute, which is used to name the node in the navigation tree.
    • Each <item> element must have a "href" attribute, which is the location (relative or absolute) of an htm that will be displayed if the node corresponding to the current <item> is clicked on in the navigation tree in the chm.
      • If href is an empty string, then there will be no page displayed for that node.
      • If href is a relative path, then it is relative to the output directory you specified with the /o flag.
      • If href is an absolute path, well, then it's absolute and that's that.
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:
  • if the package body is not wrapped, it is parsed for xml comments and the package specification is ignored.
  • if the package body is wrapped and the package specification is not wrapped, then the package specification is parsed for xml comments.
  • if both body and specification are wrapped, neither is parsed.

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:
  • Define what objects make up your data model diagrams.
  • Define the types and various properties of your external objects.
  • Define an exclusion list to keep certain database objects from appearing in the documentation.
  • Define a custom hierarchy of items to appear in the left hand navigation tree of the chm.
  • Define a html snippet for branding your generated chm.
  • Define a list of custom comments for table columns, indexes, constraints, or triggers, or for sproc and udf parameters.
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.