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 a