What attributes of SQL objects are documented by DBDocumentor?

Looking at both the sample files, the manual and running DBDocumentor™ against some of your SQL may not answer this fundamental question.  This section seeks to describe those attributes and how they are documented within DBDocumentor.  You may also want to refer to how DBDocumentor handles dynamic SQL.  If there is an aspect you are seeking, please don't hesitate to contact us, it may already be in the product, or may be planned for a future version.  Each object type will be covered in its turn. 

Note: DBDocumentor 4.00 and higher supports multiple SQL dialects, but for the sake of clarity in descriptions, Microsoft SQL Server 2000 terminology is typically used in this manual. 

Common items

Several documentation aspects are common to all SQL objects, regardless of type.  These common items include:

• The object type
• The object name
• A detailed description for the object
• Optionally the source file location for the object
• Optionally the actual source used to build the object
• If the SQL object supports RAISEERROR then any errors raised will be listed in the output (SQL dialect specific)

Data views

Data views are used to return a list of records meeting a predefined set of criteria.  The documentation options present for data views are:

• If the data view makes use of transactions
• If the data view makes use of cursors
• If the data view is the result of dynamic SQL
• If the data view is encrypted (SQL dialect specific)
• The output result set, the column name and your description for the column.  Note that if the column name is either a table/view or alias of a table/view whose entire column structure is being returned, DBDocumentor will attempt to locate the table/view and hyperlink to it.
• Any triggers defined for the view
• Any indices on the view
• Any data sources accessed by the view
• If the view is used as a data source in any objects, then those objects will be listed
• Any user defined functions used in the data view
• Any security roles on the stored procedure

Indices (Indexes)

Indices can be defined for both tables and data views.  Regardless of the object being indexed, the functionality of the index remains the same, and the documentation details are thus also the same.  The following items are documented for indices:

• Uniqueness
• Clustering of data
• If the index is on XML data (SQL dialect specific)
• The table or view on which the index acts
• The columns used to build the index

If the table or view used to build the index is also contained in the same documentation project, a hyperlink from the index to the table or view will be enabled.  If the table or view used to build the index is not contained in the same documentation project, DBDocumentor can not determine if the object is a view or a table and refer to it as simply "Object".

Stored procedures

Stored procedures are SQL scripts potentially accepting parameters, and potentially returning data.  The output data can be returned via input parameters, as a recordset or as a return value.  A stored procedure can manipulate data, make use of cursors, be transactional and execute other (nested) procedures.  The DBDocumentor produced documentation reports on all these attributes.  General documented items are:

• Transactional nature of the procedure
• If the procedure makes use of dynamic SQL
• If the procedure is encrypted (SQL dialect specific)
• If the procedure manipulates data under any of its paths of execution
• If the procedure uses cursors under any of its paths of execution
• If the procedure executes any other procedure under any of its paths of execution.  If the procedure does execute another procedure, and that procedure is present in the same project, a hyperlink will be present between the two procedures allowing you to see both who is calling an who called a given procedure.
• Any data sources accessed or modified by the stored procedure
• Any user defined functions used in the stored procedure
• Any security roles on the stored procedure

Input parameters

Input parameters to stored procedures have the following attributes in DBDocumentor:

• The assigned name
• A data type (if the data type is a user defined data type and is present in the documentation project, a hyperlink will be present between the procedure and the user defined data type)
• If the parameter is required
• If the parameter is an output parameter
• The default value of the parameter (if any)

Output data

Data can be returned from a stored procedure via input parameters (configured as output parameters), as return values, and as a recordset (dataset).  DBDocumentor has the following capabilities in describing output data:

• If output via output parameter, the options are identical to those for input parameters
• If output via return value, the numerical constant (or SQL variable used to return the constant) being returned
• If output via result set, the column name and your description for the column.  Note that if the column name is either a table/view or alias of a table/view whose entire column structure is being returned, DBDocumentor will attempt to locate the table/view and hyperlink to it.

Tables

Data tables are used to store either permanent or temporary data used in the database.  If a table is temporary, its life span can be for the life of the current database connection, or it can be global to the database in which case the life span is governed by the SQL instance.  Temporary tables can be documented by DBDocumentor, but only if there is no corresponding drop of the table.  If the same table name is used for different temporary tables, DBDocumentor will only retain the details of the last one to be processed.

The following items are documented for all table types:

• Column structure
• Column data type (if the data type is a user defined data type present in the same project, a hyperlink will be present between the table and the data type)
• Nullability of the column
• If the column is an identity column, both the seed and increment values will be recorded
• Constraints associated with the table.
• If the constraint is a DEFAULT, the default value will be listed
• If the constraint is a FOREIGN KEY, and the referred table is present in the same project, a hyperlink will be present between the two tables
• Any tables referring to the current table are listed, along with the description supplied for the index
• Any indices on the table are listed, along with the description supplied for the index
• Any objects accessing or modifying the table
• Any security roles on the table

Triggers

Triggers can be defined to fire when an underlying table or view is modified.  The items documented for triggers are:

• If the trigger becomes transactional under any path of execution
• If the trigger modifies data under any path of execution
• If the trigger makes use of cursors under any path of execution
• If the trigger makes use of dynamic SQL
• If the trigger is encrypted (SQL dialect specific)
• If the trigger executes stored procedures under any path of execution
• The table or view the trigger acts upon
• The type of trigger
• The data modification required to fire the trigger
• Any data sources accessed or modified by the trigger
• Any user defined functions used in the trigger
• Any security roles on the trigger

User defined data types

User defined data types allow the database designer to provide a more meaningful description of data by extending a base data type.  DBDocumentor only processes added data types.  If you remove a data type in a batch after DBDocumentor has processed it, the output documentation will still contain the data type definition.

• The user define name for the new data type
• The base data type
• The nullability of the new data type
• If a defined type owner is specified, the name of the owner (SQL dialect specific)
• If a default value is specified, the default value for the data type (SQL dialect specific)
• If a check constraint is defined, the value for the constraint (SQL dialect specific)
• Any stored procedures making use of the data type
• Any tables making use of the data type
• Any user defined functions making use of the data type
• Any triggers making use of the data type

User defined functions

User defined functions (or UDFs to some people) were introduced in SQL Server 2000 and provide the ability to return scalar data types (e.g. bigint, or a user defined type) and return data sets (effectively tables), all while taking parameters.  These capabilities make UDFs one of the most versatile SQL query objects available.  Consider the UDF as a cross between and a stored procedure and a parameterized data view and you've got the general idea.

The items available for documentation will vary by the type of the function, but generally are:

• The assigned name of any input parameters
• The data type associated with any input parameters. If the data type is a user defined data type and is present in the documentation project, a hyperlink will be present between the procedure and the user defined data type.
• The output return type for the function.  If the data type is a user defined data type and is present in the documentation project, a hyperlink will be present between the procedure and the user defined data type.
• If the function returns a table, the column structure of the table is defined as a result set.
• If the function makes use of transactions (SQL dialect specific)
• If the function uses cursors (SQL dialect specific)
• If the function is encrypted (SQL dialect specific)
• If the function makes use of dynamic SQL (SQL dialect specific)
• If the function is row set based and used as a data source in any objects, then these objects will be listed (SQL dialect specific)
• If the function is scalar and used in any objects, then these objects will be listed (SQL dialect specific)
• The method name and module entry point (SQL dialect specific)
• Any security roles on the function

Security roles

Security roles allow the database designer to restrict or grant access to objects thereby controlling the security risk associated with the data in the database. 

Generic batches

A generic, or ad-hoc, batch is simply a SQL batch which does not create one of the above SQL objects.  Most commonly a generic batch would be used for initial data population, data scrubbing operations, or scheduled tasks.  

Generic batches have the following attributes documented:

• Transactional nature
• Dynamic SQL usage
• Cursor usage
• Function and stored procedure calls
• Function, data view and table usage in queries

System error messages 

System messages are defined in SQL Server databases by executing sp_addmessage.  These messages are used in RAISERROR commands.  System messages are documented, and categorized in the table of contents.

System messages have the following attributes documented:

• Message identifier
• Message text
• Message severity
• Functions, data views and procedures referencing the message

Synonyms

SQL Server 2005 introduced the concept of object synonyms (or aliases).  

Synonyms have the following attributes documented:

• Parent object type and name
• Objects referencing the synonym

Microsoft Reporting Services RDL

Microsoft SQL Server 2000 and SQL Server 2005 have an optional component known as Reporting Services.  Reporting Services provides an XML based report definition language which can query a data source and generate resultant reports in many different formats.  Beginning with DBDocumentor 4.20, the RDL XML files can be processed and reported on.

RDL files have the following attributes documented:

• If the report makes use of transactions (SQL dialect specific)
• If the report uses cursors (SQL dialect specific)
• If the report makes use of dynamic SQL (SQL dialect specific)
• If the report executes stored procedures
• If the report manipulates data under any of its paths of execution
• If the report executes any stored procedures under any of its paths of execution.  If the report does execute stored procedures, and that procedure is present in the same project, a hyperlink will be present between the report and the procedure.
• Any data sources accessed or modified by the report
• Any user defined functions used in the report
• Any errors raised from the report
• Any reports drilled into (DBDocumentor 4.40)
• Any reports drilled from (DBDocumentor 4.40)
• What chart elements are using the report query (DBDocumentor 4.40)
• Who the chart author is (DBDocumentor 4.40)

 

© 2001 - 2009 Pikauba Software. All rights reserved.

DBDocumentor and SQLDocumentation are trademarks of Pikauba Software.