SqlSpec

This section gives you enough information to get started using SqlSpec, but doesn’t explain the more advanced features. See the other sections of this document for that.

Click on SqlSpec.exe to launch the GUI. You can also run it from the command line passing command line arguments, see the command line arguments section below.
Enter the connection string(s) to the database(s) you want to document. For sample connection strings, see the section below.
Hit the Go! Button. As it does its work, it will print out progress.
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 SqlSpec.exe.

Tip: If you have a database with a large number of objects (thousands, say) it can take a while for SqlSpec to finish. When learning some of the more advanced features in SqlSpec, such as data models and exclusion lists, it’s useful experiment on a small database first. That way if you go through a period of trial and error, you don’t have to wait so long between trials.

Feedback

If you have questions about SqlSpec, please have a look in the forum here: http://www.elsasoft.org/forum. If you don’t see your question answered there, post it and it will be answered. Otherwise, you can send mail to sqlspec@elsasoft.org. We’ll respond to you as quick as we can. Thanks!

If you are reporting a bug, please attach to your email a SQL script that generates a database that reproduces the bug. If you can’t do it for privacy reasons, we understand of course. If you can though, it will help us find and fix the bug!

What it does

SqlSpec is a tool for generating documentation for any database or object on the following platforms:

Microsoft SQL Server 2000/2005/2008
Microsoft Analysis Server 2005/2008
Microsoft Report Server 2005/2008
Microsoft Integration Server 2005/2008
Microsoft Access 97 and above
Oracle 9i and above
Teradata 13.0 and above*
IBM DB2 8.2 and above*
IBM Informix IDS 10 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
SQLite 3.7.3 and above*
VistaDB 3.0 and above
ENEA Polyhedra 7.0 and above
Birdstep’s Raima RDM Server 8.0 and above

* SqlSpec may work on versions before these, but it’s untested. Please try it and let us know your results.

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.

SqlSpec 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 project 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 SqlSpec.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=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en (search for “AMO” on that page).
It is not necessary to have the .NET framework installed on the server where your database resides. In fact, your server may not be running Windows at all so this would be impossible anyway in that case.
If you want to compile the output into CHM files, you need the Microsoft HTML Help Workshop installed on the client. You may already have it installed. SqlSpec 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 SqlSpec 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 SqlSpec.exe is located.
To generate documentation for Oracle schemas, you need to have an Oracle OLEDB or ODBC driver installed on the computer where SqlSpec 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 Teradata databases, you need to put a copy of Teradata.Client.Provider.dll in the same directory as SqlSpec.exe. SqlSpec uses this dll to connect to Teradata databases. You can download it here: http://www.teradata.com/downloadcenter (click the .Net Data Provider link on that page).

Sample connection strings

Here is a table of sample connection strings for each platform that SqlSpec supports. There are more examples at this website: http://www.connectionstrings.com.

Platform	Connection String	Type
MS SQL Server	server=MYSERVER;database=MYDB;uid=MYUSER;pwd=asdf;	SqlClient using SQL auth.
MS SQL Server	server=MYSERVER;database=MYDB;trusted_connection=yes;	SqlClient using Windows auth.
MS Analysis Server	Data Source=MYSERVER; Initial Catalog=Adventure Works DW	Microsoft.AnalysisServices.Server
MS Report Server	http://MYSERVER/reportserver	URL
MS Integration Server	MYSERVER	Only server name for SSIS!
MS Access	Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb	OLEDB, using Jet
MS Access	Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\mydatabase.mdb	OLEDB, using ACE
Oracle	Provider=OraOLEDB.Oracle;Data Source=XE;User Id=HR;Password=asdf	OLEDB, using OraOLEDB provider
Oracle	Provider=msdaora;Data Source=XE;User Id=HR;Password=asdf	OLEDB, using msdaora
Oracle	DSN=MYDSN;uid=HR;pwd=asdf	ODBC
MySQL	server=MYSERVER;database=sakila;uid=root;pwd=asdf	MySQL Connector/NET
PostgreSQL	Driver={PostgreSQL UNICODE};Server=localhost;Port=5432;Database=postgis;Uid=postgres;Pwd=asdf	ODBC
Sybase ASE	Driver={Adaptive Server Enterprise};server=192.168.1.4;port=5000;db=pubs2;Charset=utf8;uid=myuser;pwd=asdf;	ODBC
Sybase ASE	Provider=ASEOLEDB;Data Source=127.0.0.1:5000;Initial Catalog=pubs2;User Id=sa;Password=	OLEDB
Sybase ASE	Data Source=MYSERVER;Port=5000;Database=MYDB;uid=MYUSER;pwd=asdf	AseClient
Sybase SQL Anywhere	DSN=MYDSN;uid=sql;pwd=asdf	ODBC
DB2	Provider=IBMDADB2;Database=SAMPLE;Hostname=127.0.0.1;Protocol=TCPIP;Port=50000;Uid=db2admin;Pwd=asdf;	OLEDB
DB2	Provider=IBMDASQL;Database=SAMPLE;Hostname=SPRING;Protocol=TCPIP;Port=50000;Uid=db2admin;Pwd=asdf	ODBC
DB2	Database=SAMPLE;Server=127.0.0.1;Uid=db2admin;Pwd=asdf;	DB2.NET
Informix	Provider=Ifxoledbc.2;Password=asdf;User ID=informix; Data Source=stores_demo@SPRING; Persist Security Info=true	OLEDB
Informix	DSN=MYDSN;uid=informix;pwd=asdf	ODBC
VistaDB	Data Source=C:\VistaDB\Databases\DBDemos.vdb4;Open Mode=SharedReadOnly	VistaDBConnection
ENEA Polyhedra	Provider=PatOleDB.PatOleDB.2;Data Source=8001	OLEDB
Raima RDM Server	DSN=MYDSN;UID=admin;PWD=asdf;DATABASE=sales	ODBC
SQLite	Data Source=C:\Program Files\sqlite\test.db; Version=3;	System.Data.SQLite.SQLiteConnection
Teradata	Data Source=127.0.0.1;User ID=dbc;Password=asdf;Database=transportation;	Teradata.Client.Provider.TdConnection

Command line arguments

SqlSpec 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 SqlSpec is a console application, it’s easy for you to integrate SqlSpec 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 SqlSpec.

If you are using the GUI and want to see how you would invoke SqlSpec 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 args you would pass. If you have selected objects to document using the “exclude objects…” button, a project file will be generated for you as well. The project file is an XML file. The mapping of element names in the project file to command line args is also in the table below where applicable.

Switch	Comment	Project file element name
/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. SqlSpec supports Access 97/2000/XP/2003.	<Access>
/AX	Contains a comma-separated list of paths to xml files generated previously by SqlSpec (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 SqlSpec (for SQL Server 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 SqlSpec (for Analysis Server 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 databases.	<SqlServer>
/C	Contains a colon-separated list of connection strings to Analysis Server databases.	<AnalysisServer>
/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.	<Colorize>
/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).	<CommentsFirst>
/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.	<Charset>
/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 project 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.	<DateTimeFormat>
/DB2	Contains a colon-separated list of connection strings to DB2 databases.	<DB2>
/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.	<DB2 schema=”XXX”>
/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.
/h 0\|1	Specifies whether to preserve formatting when displaying extended properties. 0 is the default.	<PreserveFormattingOnExtendedProps>
/hhc	Specifies the path to hhc.exe. Normally SqlSpec will find this on its own, but if it can’t for some reason, you can pass it on the command line.
/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.	<HideServerNames>
/IFX	Contains a colon-separated list of OLEDB connection strings to Informix databases.	<Informix>
/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.	<Informix schema=”XXX”>
/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.
/is	Contains a colon-separated list of SSIS server names. All packages in the SSIS stores in the list will be documented.	<Ssis>
/iss	Contains a colon-separated list of SQL Server instance names. All SSIS packages in the SQL Server based package stores will be documented.	<SsisSql>
/isf	Contains a comma-separated list of paths to DTSX files. Each file represents an SSIS package to be documented.	<DTSX>
/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.	<LabelLength>
/k	Specifies the number of icons per row that appear in the reference and dependency graphs. Default is 4.	<IconsPerRow>
/ld path	Contains a path to a directory where a log file named sqlspec_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.	<MySql>
/myx	Contains a comma-separated list of paths to xml files generated previously by SqlSpec (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.	<ChmName>
/o path	Contains path to the output directory. Default is current directory.	<OutputPath>
/O	Contains a colon-separated list of OLEDB connection strings to Oracle 9i or above schemas.	<Oracle>
/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.	<Oracle schema=”XXX”>
/OX	contains a comma-separated list of paths to xml files generated previously by SqlSpec (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.	<PreserveTags>
/PG	Contains a colon-separated list of ODBC connection strings to PostgreSQL 8.0 or above databases.	<Postgresql>
/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.	<Polyhedra>
/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.	<DrawPieCharts>
/R int	Use this value to specify a number of sample rows from each table 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).	<SampleRowsTable>
/RV int	Same as /R, but for views.	<SampleRowsView>
/RDM	Specifies a list of ODBC connection strings to Raima RDM Server databases to document.	<RaimaRDM>
/RDMDDL	Specifies the path to ddlgen.exe. This is a program that ships with RDM Server that is used to generate DDL. You must pass this if you want to see the DDL for tables, indexes, and joins in your RDM Server docs.
/RDMDDLU	Specifies the username passed to ddlgen.exe. Required if you pass /RDMDDL.
/RDMDDLP	Specifies the password passed to ddlgen.exe. Required if you pass /RDMDDL.
/rn	Specifies a name for the root node in the JavaScript treeview. Default value is “Database Documentation”
/rs	Specifies a list of URLs to Microsoft Report Server (SSRS) instances to document, separated by commas.	<ReportServer>
/rsu	Specifies the username to use when connecting to SSRS instances. If not specified, SqlSpec will connect to SSRS using the windows credentials that SqlSpec.exe is running with. Note that SSRS must be configured to allow Basic Authentication when using this option.
/rsp	Specifies the password to use when connecting to SSRS instances. If not specified, SqlSpec will connect to SSRS using the windows credentials that SqlSpec.exe is running with. Note that SSRS must be configured to allow Basic Authentication when using this option.
/rsd	Specifies the windows domain to use when connecting to SSRS instances. To be used when passing /rsu and /rsp if specifying a domain account. If the user is not a domain users, don’t pass this arg.
/snc	Specifies the selected node color in the JavaScript treeview. Default value is “gray”. Other possible values are blue, green, red, etc.
/sn 0\|1	Specifies whether to use short one-part names in the documentation instead of longer two- and three- part names. Default is 0. If 1, then a table named ‘[dbo].[authors]’ would appear as ‘authors’ when it is referenced in the documentation. This can lead to some ambiguity if you have objects with the same name in different schemas. On the other hand the one-part names are more readable.	<ShortNames>
/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.	<MaxCharsForSampleRowCell>
/SY	Contains a colon-separated list of AseClient connection strings to Sybase ASE databases.	<SybaseAse>
/svg 0\|1	1 = generate SVG diagrams for the foreign key and dependency graphs. This is useful if you want to support browsers like Firefox, Opera, Safari, etc.	<DrawSvg>
/vml 0\|1	1 = generate VML diagrams. VML is only supported by Internet Explorer and the CHM viewer. If you want to support rendering diagrams in all browsers, pass 1 for both /vml and /svg.	<DrawVml>
/gv 0\|1	1 = use GraphViz to render data model diagrams. GraphViz is available for free here: http://www.graphviz.org	<UseGraphViz>
/gp	Specifies path to the folder containing GraphViz executables, in particular dot.exe	<GraphVizBinPath>
/ga 0\|1	1 = create data model diagrams for all objects in your databases. 0 is default.	<GraphAllObjects>
/spt int	If greater than zero, then each tab in the DDL will be replaced with this many spaces. Default is 0, meaning don’t replace tabs with spaces.	<SpacesPerTab>
/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.	<Timeout>
/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.	<TabularDependencies>
/td	Contains a colon-separated list of connection strings to Teradata databases.	<Teradata>
/threads int	Specifies the number of worker threads to use when transforming XML. If you have multiple processors on the machine where SqlSpec runs, you can put them to use by specifying an value here larger than 1. Default is 1.	<Threads>
/VDB	Contains a list of connection strings to VistaDB databases to document. See the VistaDB section below for an example.	<VistaDB>
/wn int	Specifies a positive integer for a number of hours. Any object that has changed between when you run SqlSpec 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.	<WhatsNew>
/x 0\|1	1 = parse xml comments. Default is 0.	<ParseXmlComments>
/X 0\|1	1 = delete all output files except for the CHM and sqlspec_progress.log. Default is 0.
/?	Show this help page.

------------------------------------------------------------------------

the following args are all for filtering the objects that get documented

each arg takes 1 or 0 - pass 1 to document the object type in question,

and pass 0 to exclude it.

------------------------------------------------------------------------

/assembly pass 1 to document assemblies.

/asymmetrickey pass 1 to document asymmetric keys

/certificate pass 1 to document certificates

/column pass 1 to document columns

/columndependency pass 1 to document column dependencies

/constraint pass 1 to document constraints

/cube pass 1 to document cubes

/dbproperty pass 1 to document database properties

/datasource pass 1 to document data sources

/datasourceview pass 1 to document data source views

/ddl pass 1 to document DDL for database objects

/default pass 1 to document defaults

/dependency pass 1 to document dependencies

/parseddl pass 1 to parse DDL for dependencies

/dimension pass 1 to document dimensions

/domain pass 1 to document domains

/endpoint pass 1 to document endpoints

/extendedproperty pass 1 to document extended properties

/foreignkey pass 1 to document foreign keys

/fulltext pass 1 to document full text catalogs

/function pass 1 to document functions

/index pass 1 to document indexes

/job pass 1 to document jobs

/login pass 1 to document logins

/mview pass 1 to document materialized views

/mining pass 1 to document mining models

/objectsize pass 1 to document object sizes

/package pass 1 to document packages

/partition pass 1 to document partitions

/permission pass 1 to document permissions

/policy pass 1 to document policies

/procedure pass 1 to document procedures

/role pass 1 to document roles

/rule pass 1 to document rules

/schema pass 1 to document schemas

/sequence pass 1 to document sequences

/serverproperty pass 1 to document server properties

/statistic pass 1 to document statistics

/symmetrickey pass 1 to document symmetric keys

/synonym pass 1 to document synonyms

/systemobject pass 1 to document system objects

/table pass 1 to document tables

/tableusage pass 1 to document table usage by views, procs, functions, etc

/tableprops pass 1 to document detailed properties about tables and views

/trigger pass 1 to document triggers

/user pass 1 to document users

/type pass 1 to document types

/view pass 1 to document views

/xmlschema pass 1 to document xml schema collections

Supported Platforms

SqlSpec supports all major DBMS platforms. The sections below explain how to generate documentation for each of them using the command line interface. For more examples of connection strings to these various platforms, see http://www.connectionstrings.com.

Microsoft SQL Server 2000/2005/2008

Here are some examples for invoking SqlSpec against SQL Server databases:

Example command lines:

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

SqlSpec.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, documentation will be generated from two databases and placed into a single MyDocs.chm file.

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

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.

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

Microsoft Analysis Server 2005/2008

Starting with SqlSpec 3.0, you can document any Analysis Server 2005 or 2008 database using SqlSpec. To do it, use the /C command line arg. Example command line:

SqlSpec.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:

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

Microsoft Report Server 2005/2008

Starting with SqlSpec 5.0, you can document any Report Server 2005/2008 instance using SqlSpec. To do it, use the /rs command line arg. Example command line:

SqlSpec.exe /rs http://SPRING/reportserver /color 1 /o c:\output

To specify the user/pass SqlSpec will use to connect to SSRS, use the /rsu and /rsp switches:

SqlSpec.exe /rs http://SPRING/reportserver /color 1 /o c:\output /rsu MYDOMAIN\Jesse /rsp Password01!

You can also combine the spec for a SQL Server database and a Report Server database into a single chm. If you additionally specify that you want cross-server dependencies documented, SqlSpec will create dependencies between all the tables, views, procs, etc in the SQL Server database that are referenced by reports on the Report Server. This is very useful for dependency hunting!

SqlSpec.exe /rs http://SPRING/reportserver /c server=SPRING;database=AdventureWorks;trusted_connection=yes; /color 1 /o c:\output /crossserver 1

Microsoft Integration Server 2005/2008

Starting with SqlSpec 6.0, you can document Integration Server 2005/2008 (SSIS) packages using SqlSpec.

Example command line using /is to specify a server name where Intergration Server is running. Use this to document packages in the SSIS package store (to document more than one server, pass a colon-separated list):

SqlSpec.exe /is SERVER01:SERVER02

To document packages in a SQL Server store, use the /iss switch and specify a SQL Server instance name (to document more than one, pass a colon-separated list):

SqlSpec.exe /iss SERVER01\KATMAI:SERVER02\YUKON

To document packages on the file system, use the /isf switch and pass a path to a dtsx file. To document more than one, use a comma separated list:

SqlSpec.exe /isf c:\packages\mypackage01.dtsx,c:\packages\mypackage02.dtsx

Microsoft 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.

Example command line:

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

SqlSpec will first attempt to use Data Access Objects (DAO) for documenting Access databases. This will fail if you don’t have DAO360.dll installed and registered on the client computer where SqlSpec runs. If it does fail, SqlSpec will let you know and then proceed to document your Access database using an OLEDB API called GetOleDbSchemaTable.

The reason we use two methods is because DAO is superior to GetOleDbSchemaTable for Access because GetOleDbSchemaTable fails in some circumstances (for example when you have linked tables to remote data sources in your Access database). However, DAO is not always installed – GetOleDbSchemaTable is used in that case as a fallback.

Normally DAO360.dll is installed here: C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.dll

If you are up to date with Windows service packs, you may have it already. If you do not have DAO installed on your computer, you can get it here: http://support.microsoft.com/kb/q239114. If you have DAO, but it is not registered, you can register it with regsvr32.exe like this:

open a console window
cd to the directory where DAO360.dll is located.
type “regsvr32 DAO360.dll” and press enter
you should see a confirmation that the DLL was registered successfully.

MySQL

Starting with SqlSpec 3.3, you can document any MySQL 5.0 and above database using SqlSpec. To do it, use the /my command line arg. SqlSpec 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 SqlSpec.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 SqlSpec.exe resides.

Example command line:

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

Teradata

Starting with SqlSpec 6.0, you can document any Teradata 13.0 and above database using SqlSpec. To do it, use the /td command line arg. SqlSpec uses Teradata.Client.Provider.dll for connecting to Teradata, which you can download here: http://www.teradata.com/downloadcenter. Note that Teradata.Client.Provider.dll must reside in the same directory as SqlSpec.exe. Teradata.Client.Provider.dll is the assembly that implements an ADO.NET interface that SqlSpec uses to connect to Teradata. Once you install it, just copy Teradata.Client.Provider.dll to where SqlSpec.exe resides.

Example command line:

SqlSpec.exe /td "Data Source=127.0.0.1; User ID=dbc; Password=asdfqwerty; Database=DBC;"

Oracle

Starting with SqlSpec 3.4, you can document any Oracle 9i and above schema using SqlSpec. 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:

SqlSpec.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.

SqlSpec.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

Starting with SqlSpec 3.5, SqlSpec will document any DB2 database. It has been thoroughly tested on DB2 8.2, but may work on earlier versions as well. If you try it on an earlier version and it doesn’t work, let us know and we’ll help you to get it working.

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

SqlSpec.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:

SqlSpec.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

Starting with SqlSpec 3.5, you can document any PostgreSQL 8.0 and above database using SqlSpec. To do it, use the /PG command line arg. SqlSpec uses ODBC for connecting to PostgreSQL, so you need to install an ODBC driver for PostgreSQL on the client machine where SqlSpec 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.

SqlSpec.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

Starting with SqlSpec 3.6, you can document a Sybase ASE 12.0.0.4 and above database using SqlSpec. To do it, use the /SY command line argument. SqlSpec uses Sybase.Data.AseClient.dll for connecting to Sybase ASE, so you need to install it on the client machine where SqlSpec 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.

SqlSpec.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 SqlSpec 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 SqlSpec.exe is located.

After following these steps, SqlSpec should be able to document Sybase ASE databases.

Sybase SQL Anywhere

Starting with SqlSpec 4.0, you can document a Sybase SQL Anywhere 10 database. Older versions of SQL Anywhere may work as well, but only version 10 has been tested. If you have an older version of a SQL Anywhere database, please try it out and let us know your results. If it doesn’t work, we’ll work with you to get it working if possible!

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:

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

Informix

Starting with SqlSpec 3.7, you can document any Informix 9.4 and above database using SqlSpec. 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.

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

SQLite

Starting with SqlSpec 6.0, you can document SQLite databases using SqlSpec. SQLite is a free, in-process, lightweight, public domain database engine available here: http://www.sqlite.org. Its authors claim it is the most widely deployed database engine in the world: http://www.sqlite.org/mostdeployed.html.

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

Finally, SqlSpec requires that a copy of System.Data.SQLite.dll is located in the same directory as SqlSpec.exe. This is the library SqlSpec uses to connect to a SQLite database. You can download it from here: http://sqlite.phxsoftware.com

SqlSpec.exe /sqlite "Data Source=C:\Program Files\sqlite\test.db; Version=3;" /o c:\vistadb_specs

VistaDB

Starting with SqlSpec 3.7, you can document VistaDB 3.0 databases using SqlSpec. 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 character you specify in the /delim argument. If /delim is not passed, a comma is assumed.

Finally, SqlSpec requires that a copy of VistaDB.NET20.dll is located in the same directory as SqlSpec.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.

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

Polyhedra

Starting with SqlSpec 3.7, you can document Polyhedra databases using SqlSpec. 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.

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

Raima RDM Server

Starting with SqlSpec 4.1, you can document Raima RDM Server databases using SqlSpec. To do it, use the /RDM command line argument. RDM Server is a RDBMS made by Birdstep Technology.

Below is a sample of how to document a RDM Server database. The /RDM flag contains a list of ODBC connection strings, separated by colons if you want to document more than one database.

If you want to include DDL for tables, indexes, and joins, you must also pass the path to ddlgen.exe in the /RDMDDL flag, as well as the username and password to use in the call to ddlgen.exe. These are passed in the /RDMDDLU and /RDMDDLP flags, as in the example below. If you do not pass these three flags, DDL for tables, indexes, and joins will not appear in your docs.

SqlSpec.exe /RDM "DSN=RDMServer;UID=admin;PWD=adminpass;DATABASE=sales;" /RDMDDL "C:\Program Files\RDMs8.1\bin\ddlgen.exe" /RDMDDLU admin /RDMDDLP secret

Annotating your database with comments

SqlSpec includes a GUI for editing extended properties, or adding comments to your database objects. This GUI currently supports SQL Server, Oracle, and PostgreSQL. In the case of SQL Server, the comments are stored as extended properties. In the case of Oracle and PostgreSQL, they are persisted in your database using the “COMMENT ON <object> IS <comment>” syntax. To invoke the editor, launch SqlSpec and click on the “Add comments” link in the left hand pane.

To use the GUI:

Launch SqlSpec and click on the “Add comments” link in the left hand pane.

A new GUI will pop up. This is the “add comments” GUI.

Choose the Platform your DBMS is on in the “Platform” drop down.

If you chose SQL Server, you can choose a name for the extended properties you’ll be editing. By default the properties you edit will be named MS_Description (recommend to leave this as is).

Type the connection string to your database in the connection string textbox.
Click the “Fetch!” button.
You will then see the list view get populated with your objects, with comments in the Comment column if they have them.
Choose an object and edit the comment for it in the “Comment” text box.

Repeat until you have commented all the objects you want.

Click the Save button.

Adding comments via script

This section applies to SQL Server only.

If you want descriptive comments for each database object to appear in your documentation, and you don’t want to use the GUI above, then you can add them via SQL scripts. 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 SqlSpec 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 nice 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 nice 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 nice comment about the @percentage param',

'user', dbo,

'procedure', byroyalty,

'parameter', '@percentage'

-- add some xml comments for a table. using this method you can add

-- xml comments to objects that don't store their DDL on the server

-- in sys.comments, such as tables.

EXEC sp_addextendedproperty

'xml_comment','

<summary>This is just a comment to give a summary of what the jobs table is for.</summary>

<log revision="1.0" date="08/18/2007" bug="none" email="jesse">Created</log>

<log revision="1.1" date="09/19/2007" bug="1234" email="jesse">fixed bug 420247</log>

</historylog>

<scope>internal</scope>

<description>here is some sample code</description>

<code>select * from jobs</code>

</sample>

<description>some more samples...</description>

<code>select top 10 * from jobs</code>

</sample>

</samples>',

'schema', dbo,

'table', jobs

You can add comments to table and view columns, stored procedures, user defined functions, etc in this way. See SQL Books Online http://www.microsoft.com/sql/techinfo/books.mspx for more info on sp_addextendedproperty, or try google: http://www.google.com/search?q=sp%5faddextendedproperty.

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 SqlSpec.

In the last example, an extended property is used to add XML comments to a table.

Exclusion lists

By default, SqlSpec 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 project file that SqlSpec uses. Here’s an example:

<!--

For MYSERVER.MyDatabase, we will exclude

spGetData, tableEmployee, viewSomeStuff, and fnDoWork.

For spGetData, we will exclude from the docs the code only, and not

other details about the sproc (such as its params, etc).

-->

</database>

<!--

For MYSERVER.SomeOtherDatabase, we will exclude

spAddRowToTable and spCountRowsInTable.

-->

</database>

</server>

<!--

For OTHERSERVER.OtherDatabase, we will exclude spDoStuff.

-->

</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 <SqlSpec> element in the project 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. You do it like this: if you want to exclude all the DDL for all objects from your docs, your <exclude> element would look like this:

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:

This would exclude the DDL for all tables and views:

This would exclude the MDX and XMLA code in an Analysis Server database:

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

Here’s a table 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
User	USER
Login	LOGIN
Role	ROLE
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
XMLA	XMLA code (Analysis Server)
MDX	MDX code (Analysis Server)

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 exist in Oracle and DB2, but not in SQL Server.

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 project 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 SqlSpec, dependencies are used (in addition to primary/foreign key relationships) to link objects together in the diagram.

SqlSpec 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:

<model name="Pubs stuff"

description="This data model shows objects in the pubs database"

iconsPerRow="6"

seed="1"

allowOverlap="0"

horizontalSpace="75"

verticalSpace="75"

maxLabelLength="12">

</database>

</server>

</model>

</database>

</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:

o The “name” attribute is required and specifies the name of the data model.

o The “description” attribute is optional. It should contain some longer text to describe the model.

o 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.

o The “seed” attribute is an integer that is used to seed a random number generator that is used in the icon placement algorithm that SqlSpec 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.

o 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.

o The “horizontalSpace” attribute specifies how much space you want to leave between icons in the horizontal direction. Default is 80.

o The “verticalSpace” attribute specifies how much space you want to leave between icons in the vertical direction. Default is 80.

o 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 fill 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 <SqlSpec> element in the project file.
The “type” attribute is required on the server element. Allowed values are:

o SqlServer

o AnalysisServer

o Oracle

o MySQL

o PostgreSQL

o DB2

o Sybase

o Access

o Informix

o VistaDB

o Polyhedra

Since you probably don’t want to type in all this xml by hand, SqlSpec will create an xml file for you in the output directory called allobjects_datamodel.xml each time it runs. This is a sample project 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 n²) 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. In my experience, it takes a few seconds for ~10 icons, about a minute for ~20, and several minutes for 50. In any case, if you data model has more than 50 objects in it, you probably need to rethink your data model anyway. J

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 SqlSpec 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 project 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>

To note:

Under the <definitions> element, you may have any number of <object> elements.
Each <object> element declares a type of external object.

o 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.

o 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 SqlSpec 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.

o The “name” attribute is a human readable name for your external object type. It can be whatever you want.

o 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”, but if you have “Octopus” for the name, then “Octopi” should be the plural.

o 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 project 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 SqlSpec output is actually defining each external object you want represented. That is also done via XML. Object definitions must be in XML files separate from the project 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:

<!--

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.

-->

<div>put some html here describing Com Object number 1...</div>

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.

o Each <object> element must have an “xtype” attribute and a “name” attribute, both values case sensitive.

o The value of the “xtype” attribute must match one of the xtypes in the <definitions> element in your project file.

o The value of the “name” element can be anything you want.

o 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.

o The “objName” attribute contains the name of an object.

o The “xtype” attribute contains the xtype of the object referred to by the “objName” attribute.

o if xtype and objName identify an object in a database, an not an external object, you must further specify these attributes:

1. The “database” attribute specifies the name of the database that the object identified by objName lives in.

2. The “server” attribute specifies the name of the server that the database identified by the “database” attribute.

3. the “serverType” says what type of server it is. It can have these values:

1. SqlServer

2. Oracle

3. DB2

4. Sybase

5. MySQL

6. PostgreSQL

7. Access

8. AnalysisServer

o The “dependentObjectName” attribute contains the name of an object that depends on the object referred to by “objName”.

o 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 SqlSpec. 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 project file, again via XML. Here’s an example:

</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 be come 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 SqlSpec. 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.

o Each <item> element must have a “name” attribute, which is used to name the node in the navigation tree.

o 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, SqlSpec 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>

<log revision="1.0" date="08/18/2003" bug="1234" email="jesse">Created</log>

<log revision="1.1" date="09/19/2003" bug="1234" email="jesse">fixed bug 420247</log>

</historylog>

</recordset>

</recordset>

</recordset>

</recordset>

</returns>

<description>here is some sample code</description>

<code>exec sp_SampleSproc @p1=1234, @p2=1.223, @p3=@blahblahblah</code>

</sample>

<description>some more samples...</description>

<code>exec sp_SampleSproc @p1=5678</code>

</sample>

</samples>

<keyword>some keyword</keyword>

<keyword>some other keyword</keyword>

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 SqlSpec. 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 SqlSpec help file

for details).

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>

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

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. For an example, see this post: http://www.elsasoft.org/forum/topic.asp?TOPIC_ID=12.
<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.
<keyword>	You can use this to add keywords to your object, which will be placed in the index of the CHM. Add as many keywords as you like for each object.
<dependency>	Used to specify dependencies of your object on other objects. It is only necessary if the dependency would not otherwise be known to SqlSpec. 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 SqlSpec 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.

Note that for some types of objects, such as tables in SQL Server, you can’t add the xml comments to the DDL directly because SQL Server does not store the DDL for tables on the server in syscomments. Because of this, if you want to add XML comments for a table you have to use extended properties. Specifically, you need to add an extended property with the name “xml_comment”. See the descriptive comments section for an example of how to do this.

XML Comments in Oracle packages

You can also put xml comments in your 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, as in the example below. 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.

For an even more verbose sample of how to add xml comments to Oracle packages, which shows some overloaded procedures as well as some private procedures and functions, see http://www.elsasoft.org/samples/oracle_package_with_overloads_and_private_routines.sql.txt.

-- Example of an Oracle package using xml comments.

-- First the package specification. You can put xml comments in here,

-- but they will be ignored unless the package body is wrapped.

CREATE OR REPLACE PACKAGE emp_mgmt AS

FUNCTION hire (last_name VARCHAR2, job_id VARCHAR2,

manager_id NUMBER, salary NUMBER,

commission_pct NUMBER, department_id NUMBER)

RETURN NUMBER;

END emp_mgmt;

-- the package body. here is where we will put our xml comments

CREATE OR REPLACE PACKAGE BODY emp_mgmt AS

tot_emps NUMBER;

-- first start with some comments about the package in general

-- <summary>comment about the package as a whole</summary>

-- <historylog>

-- <log weather="rainy" revision="1.0" date="08/18/2003" bug="1234" email="jesse">Created this package</log>

-- <log weather="sunny" revision="1.1" date="09/19/2003" bug="1234" email="jesse">fixed bug 420247</log>

-- </historylog>

-- <scope>This stuff in this package should be restricted to principals that have human resources related duties.</scope>

-- <samples>

-- <sample>

-- <description>here is some sample code</description>

-- <code>exec sp_SampleSproc @p1=1234, @p2=1.223, @p3=’blahblahblah’</code>

-- </sample>

-- </samples>

-- now on to the procs and functions implemented by this package.

-- each function or procedure needs to be wrapped in an object tag,

-- and each object tag needs two attributes:

-- 1. xtype = FN or P (FN=function, P=procedure) for

-- 2. name stores the name of the object, prefixed by the name of the package it lives in.

-- all xml comments for the given object must appear between the opening and closing object tags.

-- <object xtype="FN" name="EMP_MGMT.HIRE">

-- <summary>This function adds a new employee</summary>

-- <scope>This function should be restricted to principals that have human resources related duties.</scope>

-- <historylog>

-- <log revision="1.0" date="08/18/2003" bug="1234" email="jesse">Created</log>

-- <log revision="1.1" date="09/19/2003" bug="1234" email="jesse">fixed bug 420247</log>

-- </historylog>

FUNCTION hire

(

--<parameters>

last_name VARCHAR2, -- <param required="yes" description="this parameter is for x..."/>

job_id VARCHAR2, -- <param required="yes" description="this parameter is for xx..."/>

manager_id NUMBER, -- <param required="yes" description="this parameter is for xxx..."/>

salary NUMBER, -- <param required="yes" description="this parameter is for xxxx..."/>

commission_pct NUMBER, -- <param required="yes" description="this parameter is for xxxxx..."/>

department_id NUMBER -- <param required="yes" description="this parameter is for xxxxxx..."/>

--</parameters>

)

RETURN NUMBER IS new_empno NUMBER;

BEGIN

-- <logic>get a fresh id from employees_seq</logic>

SELECT employees_seq.NEXTVAL

INTO new_empno

FROM DUAL;

--<logic>insert the new employee</logic>

INSERT INTO employees

VALUES (new_empno, 'First', 'Last','first.last@oracle.com',

'(123)123-1234','18-JUN-02','IT_PROG',90000000,00,

100,110);

--<logic>add to our internal employee counter</logic>

tot_emps := tot_emps + 1;

-- <logic>return the value we fetched from employees_seq</logic>

RETURN(new_empno);

END;

--</object>

-- add more procs or functions here, each wrapped in an object tag...

END emp_mgmt;

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 project file with a well formed HTML snippet inside it, like this:

<!--

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.

-->

<h1>Database documentation for: #DatabaseName#</h1>

<h2>This page has information about: #PageName#</h2>

<h2>Documentation generated on: #GeneratedOn#</h2>

<p>#BreadCrumb#</p>

<span style="size:20pt">Powered by

<a target="_blank" href="http://www.elsasoft.org">Yoyodyne</a></span>

</center>

</div>

</header>

<!--

put any well-formed html in here you like.

It will appear at the bottom of each page.

-->

<br/>

<span style="size:20pt">Powered by

<a target="_blank" href="http://www.elsasoft.org">Yoyodyne</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 some special strings that will be replaced with values appropriate for each page:

· #DatabaseName# is replaced with the current database name, such as “pubs”

· #PageName# gets replaced with an appropriate name for the page, such as “[dbo].[authors]”

· #BreadCrumb# is replaced with some HTML representing a breadcrumb trail such as “database – tables – [dbo].[authors]” where each element of the trail is a link.

· #GeneratedOn# is replaced with the date that the documentation was generated on.

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.

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 project file like this:

</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:

TABLE
VIEW
TRIGGER
PROCEDURE
FUNCTION
AGGREGATE
COLUMN
INDEX
CONSTRAINT
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;

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;

The columns description table in the generated docs would then look like this:

column	datatype	length	bytes	default	nulls	PK	FK	UQ	comment	foo	bar
au_id	id	11	11		no	yes				used in case of Foo.	do not forget about Bar.
au_lname	varchar	40	40		no
au_fname	varchar	20	20		no
phone	char	12	12	('UNKNOWN')	no
address	varchar	40	40		yes
city	varchar	20	20		yes
state	char	2	2		yes
zip	char	5	5		yes
contract	bit	1	1		no

Without the customComments section in the project file, the columns table would look like this (note the last two columns are missing):

column	datatype	length	bytes	default	nulls	PK	FK	UQ	comment
au_id	id	11	11		no	yes
au_lname	varchar	40	40		no
au_fname	varchar	20	20		no
phone	char	12	12	('UNKNOWN')	no
address	varchar	40	40		yes
city	varchar	20	20		yes
state	char	2	2		yes
zip	char	5	5		yes
contract	bit	1	1		no

Sample project file

You can optionally run SqlSpec with a project file, composed in XML. The project 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.
Specify all other settings that you find in the GUI.

You must pass the path to the project file to SqlSpec using the /d flag. You cannot specify a project file path using the GUI.

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

<!--

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.

-->

<model name="Pubs stuff"

iconsPerRow="12"

seed="1"

allowOverlap="0"

horizontalSpace="75"

verticalSpace="75"

maxLabelLength="12">

</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 SqlSpec 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.

-->