Doxygen is a tool for generating documentation from source code and it currently handles many languages either natively or through add-on filters. This script is one such filter that transforms T-SQL into C-like code so that Doxygen can then parse it natively.
While this means that you can benefit from the automated generation of documentation it does mean that you have to mentally translate a 'struct' back into a 'table' and reverse the type and member/variable names in declarations.
There is a separate project underway that is designed to allow Doxygen to natively handle SQL and format it in a fashion more suitable for SQL databases.
The only file you need from the package is sql2doxygen.ps1. Copy
it to somewhere suitable such as your Doxygen installation or the SQL project.
In the Doxygen configuration file for your project you'll find a section about
options for input files. Near the bottom of that section you'll find an entry
called INPUT_FILTER, set this to where the sql2doxygen.ps1
script lives.
#--------------------------------------------------------------------------- # configuration options related to the input files #--------------------------------------------------------------------------- INPUT = . . . . INPUT_FILTER = "PowerShell.exe -File C:\Path\To\sql2doxygen.ps1" . . .
The surrounding quotes are required or you'll get a cryptic error from Doxygen about not being able to find it.
In the package you'll find a subfolder called Example that contains
a Doxygen project file and a few .sql files. Assuming the Doxygen .exe is on your
path and the location is writable you can generate the example output like so:-
C:\sql2doxygen\Example> doxygen.exe Doxygen.cfg
You should see a new folder appear called Dox. Open index.html
to see the results:-
C:\sql2doxygen\Example> start Dox\index.html
The example Doxygen.cfg file uses a relative path to reference the sql2doxygen
filter script and so you must ensure the current working directory (CWD) is set
to the Example folder.
The filter is implemented as a very simple line-based parser and that means there are restrictions on how freely the SQL can be formatted. Basically each part of the declaration (table name, column, parameter, etc.) should be on a separate line. If you document your columns and parameters using the single-line comment style on the right hand side this will be natural anyway.
T-SQL supports the C-style multi-line comment style /* comment */
and its own single-line style -- comment. This means that the
first style is already supported directly by Doxygen, whereas the second is
easily translated into the C++ style // comment. Below are a few
examples of Doxygen enabled SQL comments:-
/** * This is a multi-line comment. */ /** This is a comment on one line. */ /*! This is an alternate C-style comment. */ --! The is the SQL equivalent of a C++ single-line comment --- This is the alternate style of single-line comment definition_1 /*!< This comment is to the right */ definition_2 --!< This comment is also to the right definition_3 ---< Another comment to the right
Non-Doxygen enabled comments (/* */ and --) are still supported and will be passed through the filter but ignored by Doxygen.
The filter recognises identifiers surrounded by []'s and also detects the schema name if present. The schema will be translated into a namespace and any characters in the name unsupported by C will be stripped or replaced with an '_'.
CREATE TABLE [staging].[Customer]
Will be translated to:-
struct staging::Customer
Tables are translated into C 'structs' with the columns being represented as fields (or members). The only aspects of the column definition that are passed through are the type and name; additional attributes and constraints are ignored. The following example uses comments that precede the columns:-
/*!
* This table is an example.
*/
CREATE TABLE schema.MyTable
(
/*! My first column */
Column_1 int not null primary key clustered,
--! My second column
Column_2 udt.MyType
);
This example shows the use of comments to the right of the columns:-
--!
--! This table is also an example.
--!
CREATE TABLE schema.MyTable
(
Column_1 int, /*!< My first column */
Column_2 udt.MyType --!< My second column
);
The body must be enclosed in parenthesis on separate lines but the trailing semi-colon is optional.
Stored procedures are transformed into C functions with the type and variable names switched around. Once again you can place the comment before or to the right of the argument, but they must be on separate lines. The body is denoted by the AS/GO pair which is transformed into a pair of curly braces.
/*!
* A stored procedure that does stuff.
*/
CREATE PROCEDURE MyStoredProcedure
(
@value1 tinyint, --!< The 1st argument.
@value2 varchar(10) /*!< The 2nd argument. */
)
AS
. . .
GO
The arguments must be enclosed in parenthesis and both the parenthesis and AS/GO body delimiters must be on separate lines.
Functions are handled in much the same way as stored procedures, but with the addition of the return type. Due to the return type being specified after the argument list there are a few styles of function that can be used depending on whether the function takes zero or more arguments:-
CREATE FUNCTION MyFunction() RETURNS tinyint
AS
. . .
GO
CREATE FUNCTION MyFunction()
RETURNS tinyint
AS
. . .
GO
CREATE FUNCTION MyFunction
(
)
RETURNS tinyint
AS
. . .
GO
CREATE FUNCTION MyFunction
(
@value1 tinyint, --!< The 1st argument.
@value2 varchar(10) /*!< The 2nd argument. */
)
RETURNS tinyint
AS
. . .
GO
As with stored procedures the arguments must be enclosed in parenthesis and both the parenthesis and AS/GO body delimiters must be on separate lines.
This script is freeware - you get what you pay for, nothing more, nothing less.
Please check the web site for updates.
Email: gort@cix.co.uk
Web: www.cix.co.uk/~gort
Chris Oldwood
8th February 2012