DBDocumentor ™ - Benefits

The DBDocumentor system offers numerous benefits to developers, database administrators, professional services firms, and those generally interested in documenting the schema and activity on their SQL databases.  The DBDocumentor SQL parser supports multiple DBMS SQL dialects, including: SQL Server (all versions), Firebird 1.5 and 2.0, and Sybase SQL Anywhere (ASA).  A brief discussion of the SQL objects DBDocumentor is currently designed to work with include: data views, stored procedures, tables, triggers, user defined data types, user defined functions, indices and security roles.

DBDocumentor is designed to document the SQL database objects contained in batch scripts. To minimize the impact on the database being documented, DBDocumentor is intended to run offline from the actual database by parsing the batch scripts used to define the objects in the system. 

Offline Parser Benefits

By processing the raw SQL used to define the database, DBDocumentor has several distinct benefits from other documentation solutions.

• If the DBMS supports encrypted objects such as: stored procedures, triggers, and user defined functions, those objects can be documented without exposing business logic
• Should the DBMS author decide to change any system stored procedures, or metadata tables, DBDocumentor will continue to function, assuming no changes to the SQL dialect occur.
• DBDocumentor does not need any special security or login considerations on the actual database, as it never connects to the database
• DBDocumentor does not require any connectivity with an actual database, nor does it require any DBMS components be installed on the machine performing the documentation.
• DBDocumentor is able to describe the resultant data set of stored procedures, data view and user defined functions, providing result set output is available from these SQL objects by the DBMS.
• If a stored procedure returns information via a RETURN statement, the DBDocumentor SQL parser will include that in the output
• The DBDocumentor parser is able to determine object interactions not stored in the DBMS metadata tables (e.g. table usage in stored procedures)
• The DBDocumentor parser can frequently document the structure of dynamic SQL.  Dynamic SQL is never stored in metadata tables effectively hiding it from online documentation approaches.
• Any usage of dynamic SQL is clearly indicated in the object's documentation
• Ad-hoc queries can be documented
• SQL Server Reporting Services report queries can be documented

Benefits of SQL Documentation

The use of the DBDocumentor SQL documentation tool, and its output offer several benefits including:

• The ability to document database structures in raw form. Raw form documentation refers to the parsing of SQL scripts where no comment tags currently exist.  When you first run DBDocumentor against script, raw form documentation is produced.  Raw form output includes only the objects and their attributes, but unless there is metadata descriptions for the objects, all description columns are blank.
• Isolation of non-SQL developers from any requirement to read the SQL of a procedure or function to determine what the required inputs and expected outputs might be.
• The ability to provide rich descriptions of the purpose behind procedures, their parameters, and output.
• The ability to provide rich descriptions of the purpose behind data views
• The ability to provide database administrators with a listing of procedures, that return data, modify data, use cursors or are transactional. With this information, administrators can better evaluate database performance.
• The ability to determine if SQL coding conventions are being followed. E.g. if a project must use transactions, but a data modification procedure exists that isn't transactional.
• The ability to determine if multiple result sets are being used in a query.
• The ability to provide examples of the intended use behind a procedure, user defined function or data view.
• Providing a method to isolate the query development from the critical path for projects. In object oriented design an interface contract is defined between clients and servers. By allowing the database interface to be defined, actual database query development can occur in parallel with other aspects of a project.
• The ability to define project specific groupings of objects.  This allows the consumer of the documentation the ability to see database objects in the context of their peers.
• Optionally indicate what security context is associated with an object
• Clear indication of relationships between objects.  For example, the report for a data table includes a listing of what objects are referencing it, and which are modifying it
• The ability to determine where dynamic SQL is being used
• The ability to determine if all objects which are supposed to be encrypted are in fact encrypted.  This is DBMS specific functionality
• The ability to report on temporary object usage.