Contents Diagrams Roles Schemas Tables Views Procedures Functions

Database Help Framework
for Microsoft SQL Server

Version 1.0, September 10, 2018

Contents

Introduction

Database Help Framework

Database Help Framework allows editing database documentation in Microsoft Excel and generating the documentation in HTML format.

Also, it adds the hyperlinks to the online documentation into the Actions menu.

Using the framework gives the following benefits:

- Developers edit the documentation in Microsoft Excel, as plain text.
- The documentation is stored in a database.
- The edit forms and documentation include actual metadata about roles, schemas, objects, columns, and parameters.
- The documentation is integrated with the SaveToDB add-in.

You may use built-in SaveToDB Cell Editor (Options, Show Cell Editor) to edit the help:

SaveToDB Cell Editor

The supplied help generation script supports the Markdown's formartting syntax for hyperlinks and images, H3 and H4 headers, bold and code tags.

This documentation is generated using this framework.

You may install, update, and remove Database Help Framework using SaveToDB Application Installer wizard.

You may generate a workbook to edit the help using the SaveToDB Application Workbooks wizard.

We are making a lot for developers.

We love to hear your feedback. Feel free to contact us.

Quick Start

1. Open Microsoft Excel, select the Database tab of the SaveToDB add-in.
2. Run Wizards, Application Installer.
3. Connect to your Microsoft SQL Server database and install Database Help Framework.
4. Run Wizards, Application Workbooks.
5. Generate the database_help.xlsx workbook.
6. Edit and save help for any object.
7. Click Actions, Generate Database Documentation.

Generating Documentation

You may get actual database help using the Actions menu.

To get the final documentation to publish online, use a batch file and a powershell script to get the final links and formatting.

Here is the batch file that we use to generate the planning application documentation:

@echo off
sqlcmd -S .\SQLExpress -E -d PlanningApp2 -y0 -Q "EXEC doc.xl_actions_database_documentation @language = 'en', @schema = 'x'" -f 65001 -o planning-application.htm
powershell.exe -file update-planning-application.ps1 planning-application.htm

To use this feature, run the PowerShell and set the execution policy to the Unrestricted:

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy Unrestricted

See details here.

Here is a content of the update-planning-application.ps1 file:

if ($args.Length -eq 0)
{
   echo "Usage: update-planning-application.ps1 <filename>"
   exit
}
(Get-Content $args[0]  -encoding UTF8 -Raw) |
   Foreach-Object {$_ -replace ' (http[^ <]*\.htm)'                            , ' <a href="$1" class="external">$1</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((xls)\.[A-Za-z_1-9]+)'            , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((doc)\.[A-Za-z_1-9]+)'            , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((logs)\.[A-Za-z_1-9]+)'           , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace '( |td>|<br>)((dbo25|xls25)\.[A-Za-z_1-9]+)'    , '$1<a href="#$2">$2</a>'} |
   Foreach-Object {$_ -replace ' (xls_users|xls_developers|xls_formats)'       , ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (doc_readers|doc_writers)'                    , ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (log_administrators|log_users)'               , ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (planning_app_(administrators|analysts|developers|users))', ' <a href="#role.$1">$1</a>'} |
   Foreach-Object {$_ -replace ' (xls) '                                       , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace ' (doc) '                                       , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace ' (logs) '                                      , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace ' (dbo25|xls25) '                               , ' <a href="#schema.$1">$1</a> '} |
   Foreach-Object {$_ -replace '<p>#### *([^ \n]*)( *)([^ \n]*)( *)([^\n]*?)</p>' , '<h4 id="$1$3"><a name="$1$3"></a>$1$2$3$4$5</h4>'} |
   Foreach-Object {$_ -replace '<p>### *([^ \n]*)( *)([^ \n]*)( *)([^\n]*?)</p>'  , '<h3 id="$1$3"><a name="$1$3"></a>$1$2$3$4$5</h3>'} |
   Foreach-Object {$_ -replace '<p>```(\r\n)<br>'                              , '<p><code>$1'} |
   Foreach-Object {$_ -replace '<br>```(\r\n<br>)*</p>'                        , '</code></p>'} |
   Foreach-Object {$_ -replace '!\[(.*)\]\(([A-Za-z_\-1-9\\/:\.]*\.(png|jpg|gif))\)', '<img src="$2" title="$1" alt="$1" />'} |
   Foreach-Object {$_ -replace '\[([^\]\r\n]*)\]\((https://[^ \)\r\n]*)\)', '<a href="$2" class="external">$1</a>'} |
   Set-Content -Encoding UTF8 $args[0]

The script do the following:

- adds the external class to the http links;
- adds hyperlinks to database objects;
- adds hyperlinks to database roles;
- adds hyperlinks to database schemas;
- replaces ###Header to <h3>Header</h3>;
- replaces ####Header to <h4>Header</h4>;
- replaces ``` to <code> and </code>;
- replaces ![title](url) to <img src=<url> title="title" alt="title" />;
- replaces [title](url) to hyperlinks.

You may add your rules yourself.
You have to replace the planning application names to yours.

Change History

Version 1.0, September 10, 2018

This is the first release.

Diagrams

DiagramDescription
Diagram D01Database Help Framework Data Tables
Diagram D02Database Help Framework Configuration Tables

Diagram D01. Database Help Framework Data Tables

Database Help Framework Data Tables

Database Help Framework allows editing database documentation in Microsoft Excel and generating the documentation in HTML format.

The doc.help table contains help topics.
The doc.help_sections table contains help sections like objects, schemas, roles, diagrams, pages, and properties.
The doc.history table contains database history topics.
The doc.history_sections table contains history sections like announcements, new features, improvements, and bug fixes.

The Excel application uses editable views to edit the table data.

Add users who can write the documentation to the doc_writers role.
Add users who can read the documentation to the doc_readers role.

Diagram D02. Database Help Framework Configuration Tables

Database Help Framework Configuration Tables

Database Help Framework includes complete copies of SaveToDB Framework tables.

This allows using Database Help Framework as is, without additional requirements.

The doc.objects table contains the configuration how to save data changes back to a database.
It specifies the target tables and stored procedures for INSERT, UPDATE, and DELETE operations.

The doc.handlers table contains the handler configuration.
It configures action menu items and validation lists for framework objects.

The doc.translations table contains translations of database objects, columns, parameters, and generated help headers.

The doc.formats table contains Excel table formats for framework objects.
The SaveToDB add-in applies these formats in the first connection to database objects.
Users can save and reload table formats using SaveToDB Table Format Wizard or Save Table Format and Load Table Format menu items.

The doc.workbooks table contains a configuration to generate a workbook to edit the database help.
Use the Application Workbooks wizard to generate it.

Roles

RoleDescription
doc_readersThe role includes permissions to read the database documentation.
doc_writersThe role includes permissions to read and write the database documentation.

doc_readers

The role includes permissions to read the database documentation.

Assign this role to users who can read the documentation.

See actual database permissions in the doc.xl_actions_set_role_permissions procedure.

doc_writers

The role includes permissions to read and write the database documentation.

Assign this role to users who can write the documentation.

See actual database permissions in the doc.xl_actions_set_role_permissions procedure.

Schemas

SchemaDescription
docThe schema contains Database Help Framework tables, views, and procedures.

doc

The schema contains Database Help Framework tables, views, and procedures.

Database Help Framework has two roles, doc_readers and doc_writers, which include required permission on object levels.

Tables

TableDescription
doc.formatsThis application table contains Excel table formats of the database help framework objects.
doc.handlersThis application table contains Excel event handlers of the database help framework objects.
doc.helpThis user table contains database help.
doc.help_sectionsThis application table contains help sections.
doc.historyThis user table contains database change history.
doc.history_sectionsThis application table contains database change history sections.
doc.objectsThis application table contains the configuration of saving changes.
doc.translationsThis application table contains translations of the database help framework objects.
doc.workbooksThis application table contains workbook configuration for SaveToDB Application Workbooks wizard.

doc.formats

This application table contains Excel table formats of the database help framework objects.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(20)xls.formats.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.formats.TABLE_NAME
TABLE_EXCEL_FORMAT_XMLxmlxls.formats.TABLE_EXCEL_FORMAT_XML

This table is a copy of the xls.formats table of the SaveToDB Framework to avoid external dependency.

See Configuring Table Formats.

doc.handlers

This application table contains Excel event handlers of the database help framework objects.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(20)xls.handlers.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.handlers.TABLE_NAME
COLUMN_NAMEnvarchar(128)xls.handlers.COLUMN_NAME
EVENT_NAMEvarchar(25)xls.handlers.EVENT_NAME
HANDLER_SCHEMAnvarchar(20)xls.handlers.HANDLER_SCHEMA
HANDLER_NAMEnvarchar(128)xls.handlers.HANDLER_NAME
HANDLER_TYPEvarchar(25)xls.handlers.HANDLER_TYPE
HANDLER_CODEnvarchar(max)xls.handlers.HANDLER_CODE
TARGET_WORKSHEETnvarchar(128)xls.handlers.TARGET_WORKSHEET
MENU_ORDERintxls.handlers.MENU_ORDER
EDIT_PARAMETERSbitxls.handlers.EDIT_PARAMETERS

This table is a copy of the xls.handlers table of the SaveToDB Framework to avoid external dependency.

See Configuring Event Handlers.

doc.help

This user table contains database help.

ColumnDataTypeNullComment
IDintIdentity
SECTION_IDtinyintdoc.help_sections.ID

Help section:

1 - Objects
2 - Schemas
3 - Roles
4 - Diagrams
5 - Pages
6 - Properties
TABLE_SCHEMAnvarchar(20)Object schema or special keywords: Roles, Schemas
TABLE_NAMEnvarchar(128)Object name
COLUMN_NAMEnvarchar(255)Column or parameter name.

The field must contain NULL for help rows of database objects.
LANGUAGE_NAMEchar(2)Two characters of the description language code like 'en' or 'fr'.
VERSIONnvarchar(50)Database object version for informational purposes.

The field is not used for columns and parameters.
DESCRIPTIONnvarchar(1024)Database object, column or parameter description.
COMMENTnvarchar(max)Database object complete description.

The field is not used for columns and parameters.

The table contains help topics for different areas like objects, schemas, roles, etc.

These areas have different SECTION_ID values.

The application includes several views from different areas.
These views have specific edit procedures configured in the doc.objects table.

The table has a unique index that includes fields: SECTION_ID, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, and LANGUAGE_NAME.

doc.help_sections

This application table contains help sections.

ColumnDataTypeNullComment
IDtinyintApplication-defined values (see below).

Do not change the values.
SECTIONnvarchar(50)Section of the doc.help table.

Values:

IDSECTION
1Objects
2Schemas
3Roles
4Diagrams
5Pages
6Properties

The application assigns and uses the sections programmatically.

doc.history

This user table contains database change history.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(20)Related database schema
LANGUAGE_NAMEchar(2)Two characters of the description language code like 'en' or 'fr'.
VERSIONnvarchar(50)Version of changes
SECTION_IDtinyintdoc.history_sections.ID

Section of changes:

1 - Version Title
2 - Announcements
3 - New Features
4 - Improvements
5 - Bug Fixes
SORT_ORDERtinyintSort order of the topic inside the section
DESCRIPTIONnvarchar(1024)Header line
COMMENTnvarchar(max)Change description

Use the doc.view_history view to edit data.

doc.history_sections

This application table contains database change history sections.

ColumnDataTypeNullComment
IDtinyintApplication defined values:

1 - Version title
2 - Announcements formatted as paragraphs
Other sections formatted as lists
SECTIONnvarchar(50)Name of the section of changes.

Use the doc.translations table to translate names.

Values:

IDSECTION
1Version Title
2Announcements
3New Features
4Improvements
5Bug Fixes

Use the doc.translations table to translate section names.

doc.objects

This application table contains the configuration of saving changes.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(20)xls.objects.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.objects.TABLE_NAME
TABLE_TYPEnvarchar(128)xls.objects.TABLE_TYPE
TABLE_CODEnvarchar(max)xls.objects.TABLE_CODE
INSERT_OBJECTnvarchar(max)xls.objects.INSERT_OBJECT
UPDATE_OBJECTnvarchar(max)xls.objects.UPDATE_OBJECT
DELETE_OBJECTnvarchar(max)xls.objects.DELETE_OBJECT

This table is a copy of the xls.objects table of the SaveToDB Framework to avoid external dependency.

See Configuring Query Lists.

doc.translations

This application table contains translations of the database help framework objects.

ColumnDataTypeNullComment
IDintIdentity
TABLE_SCHEMAnvarchar(20)xls.translations.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.translations.TABLE_NAME
COLUMN_NAMEnvarchar(128)xls.translations.COLUMN_NAME
LANGUAGE_NAMEchar(2)xls.translations.LANGUAGE_NAME
TRANSLATED_NAMEnvarchar(128)xls.translations.TRANSLATED_NAME
TRANSLATED_DESCnvarchar(1024)xls.translations.TRANSLATED_DESC
TRANSLATED_COMMENTnvarchar(2000)xls.translations.TRANSLATED_COMMENT

This table is a copy of the xls.translations table of the SaveToDB Framework to avoid external dependency.

You may edit the translation and add new translations for the required languages.

You may use the doc.usp_translations procedure to edit translation for multiple languages in a pivot table.

See Configuring Data Translation.

doc.workbooks

This application table contains workbook configuration for SaveToDB Application Workbooks wizard.

ColumnDataTypeNullComment
IDintIdentity
NAMEnvarchar(128)Workbook name
TEMPLATEnvarchar(255)Template workbook path or URL.

The template workbook is not necessary.
The add-in can create a workbook using the definition only.
However, you may supply the workbook to have special features.

You may use Wizards, Developer Wizard, Prepare Workbook for Distribution to remove sensitive data and passwords from the workbook.
DEFINITIONnvarchar(max)Workbook definition.

Use the Help, Workbook Information, Workbook Definition menu item to get the definition of any opened workbook.

The SaveToDB add-in allows generating workbooks using the Application Workbooks wizard.

The add-in loads the workbook configuration from this table.

This is an application table.
If you need to generate other application workbooks, use the xls.workbooks table.

See also Configuring Application Workbooks.

Views

ViewDescription
doc.view_diagramsThis view is an Excel form for editing diagram help.
doc.view_historyThis view is an Excel form for editing database history.
doc.view_objectsThis view is an Excel form for editing database object help.
doc.view_online_help_handlersThis view selects the handler configuration for the Actions menu.
doc.view_orphan_rowsThis view is an Excel form for editing orphan rows.
doc.view_pagesThis view is an Excel form for editing page help.
doc.view_propertiesThis view is an Excel form for editing HTML page properties.
doc.view_query_listThis view selects framework objects for the SaveToDB Connection Wizard and SaveToDB Query List.
doc.view_routine_columnsThis view is an Excel form for editing routine column help.
doc.view_routine_parametersThis view is an Excel form for editing routine parameter help.
doc.view_table_columnsThis view is an Excel form for editing table column help.
doc.view_view_columnsThis view is an Excel form for editing view column help.

doc.view_diagrams

This view is an Excel form for editing diagram help.

Source tables: doc.help

ColumnDataTypeComment
IDintdoc.help.ID
DIAGRAM_SCHEMAnvarchar(20)Related database schema

doc.help.TABLE_SCHEMA
DIAGRAM_NAMEnvarchar(128)Diagram name

doc.help.TABLE_NAME

Use names like Diagram 01, Diagram 02, etc.
DIAGRAM_URLnvarchar(255)Diagram image URL

doc.help.COLUMN_NAME

Note that the field has max length 255 characters.
LANGUAGE_NAMEchar(2)Two characters of the description language code like 'en' or 'fr'
VERSIONnvarchar(50)Diagram version for informational purposes
TITLEnvarchar(1024)Diagram title

doc.help.DESCRIPTION
COMMENTnvarchar(max)Diagram complete description

doc.help.COMMENT

To add a new diagram, add or copy the row.

Note that DIAGRAM_SCHEMA, DIAGRAM_NAME, DIAGRAM_URL, and LANGUAGE_NAME must be unique within the table.

doc.view_history

This view is an Excel form for editing database history.

Source tables: doc.history

ColumnDataTypeComment
IDintdoc.history.ID
EXCEL_SORT_ORDERbigintUse this column to sort rows in Microsoft Excel.

Save the changes in the table to update the field.
SECTION_SORT_ORDERtinyintThis field has a numeric value of SECTION_ID.

You may use it for sorting data or conditional formatting.
TABLE_SCHEMAnvarchar(20)Related database schema

doc.history.TABLE_SCHEMA
SECTION_IDtinyintdoc.history.SECTION_ID
LANGUAGE_NAMEchar(2)doc.history.LANGUAGE_NAME
VERSIONnvarchar(50)doc.history.VERSION
SORT_ORDERtinyintdoc.history.SORT_ORDER within a section
DESCRIPTIONnvarchar(1024)doc.history.DESCRIPTION
COMMENTnvarchar(max)doc.history.COMMENT

The value is not used with version titles.

To add a new record, add or copy the row.

doc.view_objects

This view is an Excel form for editing database object help.

Source tables: doc.help

ColumnDataTypeComment
SORT_ORDERbigintThis is an auto-generated column used to sort rows in Microsoft Excel
SECTION_IDintdoc.sections.id

1 - database objects
2 - schemas
3 - roles
6 - database properties
TABLE_SCHEMAnvarchar(128)Object schema or pseudo-schema:

database - database properties
roles - database roles
schemas - database schemas
TABLE_NAMEnvarchar(128)Object, role, schema, or property name

The database properties includes:

stylesheet_href - URL of the stylesheet
head - any value in the COMMENT column to include into the HTML head section
title - document title
TYPEnvarchar(4000)INFORMATION_SCHEMA.TABLES.TABLE_TYPE,
INFORMATION_SCHEMA.ROUTINES.ROUTINE_TYPE
LANGUAGE_NAMEvarchar(2)doc.help.LANGUAGE_NAME
VERSIONnvarchar(50)doc.help.VERSION
DESCRIPTIONnvarchar(1024)doc.help.DESCRIPTION
COMMENTnvarchar(max)doc.help.COMMENT
HAS_COMMENTSint1 - has comments
0 - has no comments

The view selects all database objects and related properties.

You cannot add new rows. You may delete rows to delete help topics.

Use the doc.view_orphan_rows view to edit object names after name changes in a database.

doc.view_online_help_handlers

This view selects the handler configuration for the Actions menu.

Source tables: doc.help

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(20)doc.help.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)doc.help.TABLE_NAME
COLUMN_NAMEnvarchar(128)NULL
EVENT_NAMEvarchar(7)Actions
HANDLER_SCHEMAnvarchar(20)doc.help.TABLE_SCHEMA
HANDLER_NAMEnvarchar(172)Online Database Help - <schema>.<name>
HANDLER_TYPEvarchar(13)HTTP or MENUSEPARATOR
HANDLER_CODEnvarchar(4000)Online help hyperlink
TARGET_WORKSHEETnvarchar(30)NULL
MENU_ORDERintHandler order in the context menu (90, 91)
EDIT_PARAMETERSbitNULL

The view adds items to the Actions menu to open the online help.

The underlying object must have help topics and the object schema must have the online_help_url value.

doc.view_orphan_rows

This view is an Excel form for editing orphan rows.

Source tables: doc.help

ColumnDataTypeComment
IDintdoc.help.ID
SECTION_IDtinyintdoc.help.SECTION_ID
TABLE_SCHEMAnvarchar(20)doc.help.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)doc.help.TABLE_NAME
COLUMN_NAMEnvarchar(255)doc.help.COLUMN_NAME
LANGUAGE_NAMEchar(2)doc.help.LANGUAGE_NAME
VERSIONnvarchar(50)doc.help.VERSION
DESCRIPTIONnvarchar(1024)doc.help.DESCRIPTION
COMMENTnvarchar(max)doc.help.COMMENT

Use this view to change object names after name changes in a database.

You may delete rows to delete help topics.

doc.view_pages

This view is an Excel form for editing page help.

Source tables: doc.help

ColumnDataTypeComment
SORT_ORDERbigintThis is an auto-generated column used to sort rows in Microsoft Excel
TABLE_SCHEMAnvarchar(128)Related database schema
TABLE_NAMEvarchar(12)Page name. See the description below.
LANGUAGE_NAMEvarchar(2)Two characters of the description language code like 'en' or 'fr'
VERSIONnvarchar(50)Page version for informational purposes.
DESCRIPTIONnvarchar(1024)Page title.

The field has name DESCRIPTION to be consistent with other tables.
COMMENTnvarchar(max)Page content

The view selects the predefined pages included into the generated documentation for every database schema:

- introduction
- diagrams
- roles
- schemas
- tables
- views
- procedures
- functions
- conclusion

You may omit the description shown as a header h3.

doc.view_properties

This view is an Excel form for editing HTML page properties.

Source tables: doc.help

ColumnDataTypeComment
SORT_ORDERbigintThis is an auto-generated column used to sort rows in Microsoft Excel
SECTION_IDintdoc.help.SECTION_ID
TABLE_SCHEMAnvarchar(128)doc.help.TABLE_SCHEMA
TABLE_NAMEvarchar(15)doc.help.TABLE_NAME
LANGUAGE_NAMEvarchar(2)doc.help.LANGUAGE_NAME
COMMENTnvarchar(max)doc.help.COMMENT

Use this view to customize the generated HTML help.

doc.view_query_list

This view selects framework objects for the SaveToDB Connection Wizard and SaveToDB Query List.

Source tables: doc.objects

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)doc.objects.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)doc.objects.TABLE_NAME
TABLE_TYPEvarchar(10)INFORMATION_SCHEMA.TABLES.TABLE_TYPE
TABLE_CODEnvarchar(max)Not used
INSERT_PROCEDUREnvarchar(max)doc.objects.INSERT_OBJECT
UPDATE_PROCEDUREnvarchar(max)doc.objects.UPDATE_OBJECT
DELETE_PROCEDUREnvarchar(max)doc.objects.DELETE_OBJECT
PROCEDURE_TYPEvarchar(9)Information column, not used:
TABLE - the add-in saves data into the specified table
PROCEDURE - the add-in saves data using the specified procedures

See also Configuring Query Lists.

doc.view_routine_columns

This view is an Excel form for editing routine column help.

Source tables: doc.help

ColumnDataTypeComment
SORT_ORDERbigintThis is an auto-generated column used to sort rows in Microsoft Excel
TABLE_SCHEMAnvarchar(128)INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)INFORMATION_SCHEMA.ROUTINE_COLUMNS.TABLE_NAME
ORDINAL_POSITIONintINFORMATION_SCHEMA.ROUTINE_COLUMNS.ORDINAL_POSITION
COLUMN_NAMEnvarchar(128)INFORMATION_SCHEMA.ROUTINE_COLUMNS.PARAMETER_NAME
DATA_TYPEnvarchar(191)INFORMATION_SCHEMA.ROUTINE_COLUMNS.DATA_TYPE
LANGUAGE_NAMEvarchar(2)Two characters of the description language code like 'en' or 'fr'
DESCRIPTIONnvarchar(1024)doc.help.DESCRIPTION
HAS_COMMENTSbit1 - has comments
0 - has no comments

The view selects all actual function output columns and input parameters.

You cannot add new rows. You may delete rows to delete help topics.

Use the doc.view_orphan_rows view to edit object names after name changes in a database.

doc.view_routine_parameters

This view is an Excel form for editing routine parameter help.

Source tables: doc.help

ColumnDataTypeComment
SORT_ORDERbigintThis is an auto-generated column used to sort rows in Microsoft Excel
ROUTINE_SCHEMAnvarchar(128)doc.help.TABLE_SCHEMA
ROUTINE_NAMEnvarchar(128)doc.help.TABLE_NAME
ORDINAL_POSITIONintINFORMATION_SCHEMA.PARAMETERS.ORDINAL_POSITION
PARAMETER_NAMEnvarchar(128)INFORMATION_SCHEMA.PARAMETERS.PARAMETER_NAME, doc.help.COLUMN_NAME
DATA_TYPEnvarchar(191)INFORMATION_SCHEMA.PARAMETERS.DATA_TYPE
PARAMETER_MODEnvarchar(10)INFORMATION_SCHEMA.PARAMETERS.PARAMETER_MODE
LANGUAGE_NAMEvarchar(2)Two characters of the description language code like 'en' or 'fr'
DESCRIPTIONnvarchar(1024)doc.help.DESCRIPTION
HAS_COMMENTSbit1 - has comments
0 - has no comments

The view selects all actual stored procedure parameters.

You cannot add new rows. You may delete rows to delete help topics.

Use the doc.view_orphan_rows view to edit object names after name changes in a database.

doc.view_table_columns

This view is an Excel form for editing table column help.

Source tables: doc.help

ColumnDataTypeComment
SORT_ORDERbigintThis is an auto-generated column used to sort rows in Microsoft Excel
TABLE_SCHEMAnvarchar(128)INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
ORDINAL_POSITIONintINFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
COLUMN_NAMEnvarchar(128)INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
DATA_TYPEnvarchar(191)INFORMATION_SCHEMA.COLUMNS.DATA_TYPE
IS_PKintINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.COLUMN_NAME
IS_IDENTITYintsys.columns.is_identity
IS_NULLABLEintINFORMATION_SCHEMA.COLUMNS.IS_NULLABLE
REFERENTIAL_COLUMNnvarchar(386)INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME
LANGUAGE_NAMEvarchar(2)Two characters of the description language code like 'en' or 'fr'
DESCRIPTIONnvarchar(1024)doc.help.DESCRIPTION
HAS_COMMENTSbit1 - has comments
0 - has no comments

The view selects all actual table columns.

You cannot add new rows. You may delete rows to delete help topics.

Use the doc.view_orphan_rows view to edit object names after name changes in a database.

doc.view_view_columns

This view is an Excel form for editing view column help.

Source tables: doc.help

ColumnDataTypeComment
SORT_ORDERbigintThis is an auto-generated column used to sort rows in Microsoft Excel
TABLE_SCHEMAnvarchar(128)INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
ORDINAL_POSITIONintINFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
COLUMN_NAMEnvarchar(128)INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME
DATA_TYPEnvarchar(191)INFORMATION_SCHEMA.COLUMNS.DATA_TYPE
REFERENTIAL_COLUMNnvarchar(386)INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
LANGUAGE_NAMEvarchar(2)Two characters of the description language code like 'en' or 'fr'
DESCRIPTIONnvarchar(1024)doc.help.DESCRIPTION
HAS_COMMENTSbit1 - has comments
0 - has no comments

The view selects all actual view columns.

You cannot add new rows. You may delete rows to delete help topics.

Use the doc.view_orphan_rows view to edit object names after name changes in a database.

Procedures

ProcedureDescription
doc.usp_translationsThis procedure is an Excel form to select and edit translations in all languages.
doc.usp_translations_changeThis procedure updates a database on cell changes of doc.usp_translations.
doc.xl_actions_database_documentationThis procedure generates the database documentation.
doc.xl_actions_set_role_permissionsThis procedure sets permissions for the doc_readers and doc_writers roles.
doc.xl_delete_help_columnThis procedure deletes a column help row.
doc.xl_delete_help_objectThis procedure deletes an object help row.
doc.xl_delete_help_parameterThis procedure deletes a parameter help row.
doc.xl_delete_help_rowThis procedure deletes a help row by id.
doc.xl_export_helpThis procedure exports help data.
doc.xl_export_settingsThis procedure exports Database Help Framework settings.
doc.xl_import_helpThis procedure imports a help row.
doc.xl_import_historyThis procedure imports a history row.
doc.xl_insert_help_diagramThis procedure inserts a diagram help row.
doc.xl_update_help_columnThis procedure updates a column help row.
doc.xl_update_help_diagramThis procedure updates a diagram help row.
doc.xl_update_help_objectThis procedure updates an object help row.
doc.xl_update_help_pageThis procedure updates a page help row.
doc.xl_update_help_parameterThis procedure updates a parameter help row.
doc.xl_validation_list_history_section_idThis procedure selects history sections to use as an Excel validation list source.

doc.usp_translations

This procedure is an Excel form to select and edit translations in all languages.

ParameterDataTypeModeComment
@fieldnvarchar(128)INThe source field of the doc.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT.

Editing data requires the SaveToDB Enterprise edition.

doc.usp_translations_change

This procedure updates a database on cell changes of doc.usp_translations.

ParameterDataTypeModeComment
@column_namenvarchar(128)INThe column name of the edited cell (expected doc.translations.LANGUAGE_NAME).
@cell_valuenvarchar(max)INA new value of the edited cell used to update for the source field.
@TABLE_SCHEMAnvarchar(128)INdoc.translations.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INdoc.translations.TABLE_NAME
@COLUMNnvarchar(128)INdoc.translations.COLUMN_NAME
@fieldnvarchar(128)INThe source field of the xls.translations table: TRANSLATED_NAME, TRANSLATED_DESC, or TRANSLATED_COMMENT.

doc.xl_actions_database_documentation

This procedure generates the database documentation.

ParameterDataTypeModeComment
@languagechar(2)INdoc.help.LANGUAGE_NAME
@schemanvarchar(128)INPossible values:
- NULL (all schemas, default)
- Schema
- Schema mask like '%25' to select schemas like dbo25 and xls25
- 'x' to select all schemas except schemas of known frameworks

You may customize this procedure to generate the required HTML.

Note that the procedure uses words from the doc.translations table.
So, you may change the required terms in an easy way.

doc.xl_actions_set_role_permissions

This procedure sets permissions for the doc_readers and doc_writers roles.

Execute this procedure if you recreated any object of the doc schema.

doc.xl_delete_help_column

This procedure deletes a column help row.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@COLUMN_NAMEnvarchar(255)INdoc.help.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME

See the configuration in the doc.objects table.

doc.xl_delete_help_object

This procedure deletes an object help row.

ParameterDataTypeModeComment
@SECTION_IDtinyintINdoc.help.SECTION_ID
@TABLE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME

See the configuration in the doc.objects table.

doc.xl_delete_help_parameter

This procedure deletes a parameter help row.

ParameterDataTypeModeComment
@ROUTINE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@ROUTINE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@PARAMETER_NAMEnvarchar(255)INdoc.help.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME

See the configuration in the doc.objects table.

doc.xl_delete_help_row

This procedure deletes a help row by id.

ParameterDataTypeModeComment
@IDintINdoc.help.ID

See the configuration in the doc.objects table.

doc.xl_export_help

This procedure exports help data.

ParameterDataTypeModeComment
@languagechar(2)INTwo characters of the documentation language code like 'en' or 'fr'.
@schemanvarchar(128)INdoc.help.TABLE_SCHEMA

Use 'x' to export help for all schemas except doc, logs, and xls.

Exporting data using this procedure is a better choice.

It uses the doc.xl_import_help procedure to merge help topics correctly.

doc.xl_export_settings

This procedure exports Database Help Framework settings.

ParameterDataTypeModeComment
@parttinyintINNULL - all
1 - doc.objects
2 - doc.handlers
3 - doc.translations
4 - doc.formats
5 - doc.workbooks
6 - doc.sections
7 - doc.history_sections
@sort_by_namesbitIN1 - sort by names
0 - sort by id (default)
@languagechar(2)INdoc.help.LANGUAGE_NAME

You may use this procedure to export the framework settings, not the help.

doc.xl_import_help

This procedure imports a help row.

ParameterDataTypeModeComment
@SECTION_IDtinyintINdoc.help.SECTION_ID
@TABLE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@COLUMN_NAMEnvarchar(255)INdoc.help.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.help.VERSION
@DESCRIPTIONnvarchar(1024)INdoc.help.DESCRIPTION
@COMMENTnvarchar(max)INdoc.help.COMMENT

The procedure is used in the scripts generated by the doc.xl_export_help procedure.

The procedure tries to update the record first. If the update fails, it inserts a new record.

doc.xl_import_history

This procedure imports a history row.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(20)INdoc.history.TABLE_SCHEMA
@LANGUAGE_NAMEchar(2)INdoc.history.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.history.VERSION
@SECTION_IDtinyintINdoc.history.SECTION_ID
@SORT_ORDERtinyintINdoc.history.SORT_ORDER
@DESCRIPTIONnvarchar(1024)INdoc.history.DESCRIPTION
@COMMENTnvarchar(1)INdoc.history.COMMENT

The procedure is used in the scripts generated by the doc.xl_export_help procedure.

The procedure tries to update the record first. If the update fails, it inserts a new record.

doc.xl_insert_help_diagram

This procedure inserts a diagram help row.

ParameterDataTypeModeComment
@DIAGRAM_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@DIAGRAM_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@DIAGRAM_URLnvarchar(255)INdoc.help.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.help.VERSION
@TITLEnvarchar(1024)INdoc.help.DESCRIPTION
@COMMENTnvarchar(max)INdoc.help.COMMENT

See the configuration in the doc.objects table.

doc.xl_update_help_column

This procedure updates a column help row.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@COLUMN_NAMEnvarchar(255)INdoc.help.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.help.VERSION
@DESCRIPTIONnvarchar(1024)INdoc.help.DESCRIPTION
@COMMENTnvarchar(max)INdoc.help.COMMENT

See the configuration in the doc.objects table.

doc.xl_update_help_diagram

This procedure updates a diagram help row.

ParameterDataTypeModeComment
@IDintINdoc.help.ID
@DIAGRAM_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@DIAGRAM_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@DIAGRAM_URLnvarchar(255)INdoc.help.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.help.VERSION
@TITLEnvarchar(1024)INdoc.help.DESCRIPTION
@COMMENTnvarchar(max)INdoc.help.COMMENT

See the configuration in the doc.objects table.

doc.xl_update_help_object

This procedure updates an object help row.

ParameterDataTypeModeComment
@SECTION_IDtinyintINdoc.help.SECTION_ID
@TABLE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.help.VERSION
@DESCRIPTIONnvarchar(1024)INdoc.help.DESCRIPTION
@COMMENTnvarchar(max)INdoc.help.COMMENT

See the configuration in the doc.objects table.

doc.xl_update_help_page

This procedure updates a page help row.

ParameterDataTypeModeComment
@TABLE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@TABLE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.help.VERSION
@DESCRIPTIONnvarchar(1024)INdoc.help.DESCRIPTION
@COMMENTnvarchar(max)INdoc.help.COMMENT

See the configuration in the doc.objects table.

doc.xl_update_help_parameter

This procedure updates a parameter help row.

ParameterDataTypeModeComment
@ROUTINE_SCHEMAnvarchar(128)INdoc.help.TABLE_SCHEMA
@ROUTINE_NAMEnvarchar(128)INdoc.help.TABLE_NAME
@PARAMETER_NAMEnvarchar(255)INdoc.help.COLUMN_NAME
@LANGUAGE_NAMEchar(2)INdoc.help.LANGUAGE_NAME
@VERSIONnvarchar(50)INdoc.help.VERSION
@DESCRIPTIONnvarchar(1024)INdoc.help.DESCRIPTION
@COMMENTnvarchar(max)INdoc.help.COMMENT

See the configuration in the doc.objects table.

doc.xl_validation_list_history_section_id

This procedure selects history sections to use as an Excel validation list source.

ParameterDataTypeModeComment
@data_languagechar(2)INThe SaveToDB add-in passes a data language selected in the SaveToDB Options dialog box.
This is a predefined parameter.

See the configuration in the doc.handlers table.

Functions

FunctionDescription
doc.get_escaped_parameter_nameThis function returns an escaped parameter name.

doc.get_escaped_parameter_name

This function returns an escaped parameter name.

ParameterDataTypeComment
Resultnvarchar(255)Escaped parameter name
@namenvarchar(128)Column name

The function converts special characters using the _xHHHH_ format, where HHHH is the hexadecimal character code.

For example, it converts a single quote to _x0027_.

You may use such parameter names in the procedures and handlers that get values from the Excel table columns.
The SaveToDB add-in uses the same conversion function to link columns and parameters.

^