SaveToDB Framework Views

SaveToDB Framework Views

ViewDescription
xls.queriesThis view selects database objects for the SaveToDB Connection Wizard and SaveToDB Query List.
xls.users
xls.view_all_translationsThis view is an Excel form for editing translation for all objects, columns, and parameters.
xls.view_developer_handlersThis view selects the handler configuration for generating views, procedures, and constraints.
xls.view_foreign_keysThis view is an Excel form for managing foreign key constraints.
xls.view_formats
xls.view_framework_objectsThis view selects SaveToDB configuration objects.
xls.view_handlers
xls.view_objects
xls.view_primary_keysThis view is an Excel form for managing primary key constraints.
xls.view_queries
xls.view_translations
xls.view_unique_keysThis view is an Excel form for managing unique constraints.
xls.view_workbooks

xls.queries

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

Source tables: xls.objects

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)sys.schemas.name, xls.objects.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)sys.objects.name, xls.objects.TABLE_NAME
TABLE_TYPEnvarchar(128)sys.objects.type, xls.objects.TABLE_TYPE
TABLE_CODEnvarchar(max)xls.objects.TABLE_CODE
INSERT_PROCEDUREnvarchar(max)xls.objects.INSERT_OBJECT
UPDATE_PROCEDUREnvarchar(max)xls.objects.UPDATE_OBJECT
DELETE_PROCEDUREnvarchar(max)xls.objects.DELETE_OBJECT
PROCEDURE_TYPEnvarchar(50)Empty column. Used to be compatible with previous SaveToDB versions.

The previous version may have values:

TABLE - the add-in saves data into the specified table
UPDATABLE VIEW - the add-in saves data into the view itself
PROCEDURE - the add-in saves data using the specified procedures
MERGE - the add-in inserts new records and updates existing records

The native Excel connection wizard shows tables and views only.

The SaveToDB connection wizard shows stored procedures also.

The SaveToDB add-in hides procedures that do not return results.

It does this in several ways.
For example, it excludes procedures with the _change, _insert, _update, and _delete suffixes.

The second task is to hide technical objects like views or procedures that select ribbon parameter values or Excel validation lists.

The add-in excludes such objects using naming conventions.
For example, the add-in does not show objects with the xl_, view_xl_, and usp_xl_ prefixes.

You may find the complete rules in the view code.

The add-in uses these rules in the default built-in query list.

You may use this view as a base for derived views to filter the objects you need.

For example, you may create query lists specific to different business areas.

This view selects database objects from the sys.objects table and non-database objects configured in the xls.objects table.

Unlike SaveToDB 7, SaveToDB 8 reads the xls.objects table directly.
So, you do not need to select the saving configuration for database objects.

See also Configuring Query Lists.

xls.users

ColumnDataTypeComment
usernvarchar(128)
rolenvarchar(128)

xls.view_all_translations

This view is an Excel form for editing translation for all objects, columns, and parameters.

Source tables: xls.translations, xls.view_framework_objects

ColumnDataTypeComment
SECTIONint1 - tables
2 - routines
3 - columns and parameters on schema level
4 - columns of tables and views
5 - function columns of returned tables
6 - parameters
SORT_ORDERbigintSort order within a section
TRANSLATION_TYPEvarchar(9)object, schema, column, or parameter
TABLE_TYPEnvarchar(20)table, view, function, or procedure
TABLE_SCHEMAnvarchar(128)xls.translations.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.translations.TABLE_NAME
COLUMN_NAMEnvarchar(128)xls.translations.COLUMN_NAME
LANGUAGE_NAMEvarchar(2)xls.translations.LANGUAGE_NAME
TRANSLATED_NAMEnvarchar(128)xls.translations.TRANSLATED_NAME
TRANSLATED_DESCnvarchar(1024)xls.translations.TRANSLATED_DESC

Unlike the xls.translation table, the view selects all objects suitable for translation.

Use it to check and edit translations.

xls.view_developer_handlers

This view selects the handler configuration for generating views, procedures, and constraints.

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)Configured object schema
TABLE_NAMEnvarchar(128)Configured object name
COLUMN_NAMEnvarchar(128)Column or parameter name of the configured object
EVENT_NAMEvarchar(7)Actions
HANDLER_SCHEMAvarchar(3)xls
HANDLER_NAMEvarchar(37)Handler object name
HANDLER_TYPEvarchar(13)CODE or MENUSEPARATOR
HANDLER_CODEnvarchar(max)SQL code of the handler object
TARGET_WORKSHEETnvarchar(128)NULL
MENU_ORDERintHandler order in the Actions menu (200-220)
EDIT_PARAMETERSbit1 - Always suggest the Edit Parameters dialog box

This view adds items to the Actions menu for every database object to generate edit procedures and handlers.

This view is accessible for the xls_developers role members only.

You may use DENY SELECT to disable this feature.

xls.view_foreign_keys

This view is an Excel form for managing foreign key constraints.

ColumnDataTypeComment
SORT_ORDERbigintSort order
SCHEMAnvarchar(128)Table schema
TABLEnvarchar(128)Table name
COLUMNnvarchar(128)Column name
POSITIONintOrdinal position
REFERENTIAL_SCHEMAnvarchar(128)Referenced table schema
REFERENTIAL_TABLEnvarchar(128)Referenced table name
REFERENTIAL_COLUMNnvarchar(128)Referenced column name
CONSTRAINTnvarchar(128)Constraint name
ON_UPDATEnvarchar(60)CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)
ON_DELETEnvarchar(60)CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION)

Use this view to create or drop foreign key constraints using the context menu.

Use it after publishing tables using the SaveToDB Publish Wizard.

xls.view_formats

Source tables: xls.formats

ColumnDataTypeComment
IDintxls.formats.ID
TABLE_SCHEMAnvarchar(128)xls.formats.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.formats.TABLE_NAME
TABLE_EXCEL_FORMAT_XMLxmlxls.formats.TABLE_EXCEL_FORMAT_XML

xls.view_framework_objects

This view selects SaveToDB configuration objects.

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)Table schema
TABLE_NAMEnvarchar(128)Table name
TABLE_VERSIONintSaveToDB configuration version

This view contains the code used in the SaveToDB Add-In, DBEdit, ODataDB, and DBGate applications to detect SaveToDB Framework objects.

xls.view_handlers

Source tables: xls.handlers

ColumnDataTypeComment
IDintxls.handlers.ID
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

xls.view_objects

Source tables: xls.objects

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)xls.objects.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.objects.TABLE_NAME
TABLE_TYPEnvarchar(128)xls.objects.TABLE_TYPE
INSERT_OBJECTnvarchar(max)xls.objects.INSERT_OBJECT
UPDATE_OBJECTnvarchar(max)xls.objects.UPDATE_OBJECT
DELETE_OBJECTnvarchar(max)xls.objects.DELETE_OBJECT

xls.view_primary_keys

This view is an Excel form for managing primary key constraints.

ColumnDataTypeComment
SORT_ORDERbigintSort order
SCHEMAnvarchar(128)Table schema
TABLEnvarchar(128)Table name
COLUMNnvarchar(128)Column name
POSITIONintOrdinal position
CONSTRAINTnvarchar(128)Constraint name
INDEX_POSITIONintColumn position in the index
IS_DESCENDINGbitNULL - ASC
1 - DESC

Use this view to check tables without primary keys.

Use the context menu to drop and create constraints.

xls.view_queries

Source tables: xls.queries

ColumnDataTypeComment
TABLE_SCHEMAnvarchar(128)xls.queries.TABLE_SCHEMA
TABLE_NAMEnvarchar(128)xls.queries.TABLE_NAME
TABLE_TYPEnvarchar(128)xls.queries.TABLE_TYPE
INSERT_PROCEDUREnvarchar(max)xls.queries.INSERT_PROCEDURE
UPDATE_PROCEDUREnvarchar(max)xls.queries.UPDATE_PROCEDURE
DELETE_PROCEDUREnvarchar(max)xls.queries.DELETE_PROCEDURE
PROCEDURE_TYPEnvarchar(50)xls.queries.PROCEDURE_TYPE

xls.view_translations

Source tables: xls.translations

ColumnDataTypeComment
IDintxls.translations.ID
TABLE_SCHEMAnvarchar(128)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

xls.view_unique_keys

This view is an Excel form for managing unique constraints.

ColumnDataTypeComment
SORT_ORDERbigintSort order
SCHEMAnvarchar(128)Table schema
TABLEnvarchar(128)Table name
COLUMNnvarchar(128)Column name
POSITIONintOrdinal position
CONSTRAINTnvarchar(128)Constraint name
INDEX_POSITIONintColumn position in the index
IS_DESCENDINGbitNULL - ASC
1 - DESC
IS_INCLUDEDbitNULL - key column
1 - nonkey column

Use this view to create or drop unique constraints using the context menu.

The SaveToDB add-in uses fields with unique constraints as names in validation lists.
Check the referenced tables and add unique constraints for the desired fields.

xls.view_workbooks

Source tables: xls.workbooks

ColumnDataTypeComment
IDintxls.workbooks.ID
NAMEnvarchar(128)xls.workbooks.NAME
TEMPLATEnvarchar(255)xls.workbooks.TEMPLATE
DEFINITIONnvarchar(max)xls.workbooks.DEFINITION
TABLE_SCHEMAnvarchar(128)xls.workbooks.TABLE_SCHEMA