Contents Diagrams Roles Schemas Tables Views Procedures Functions

SaveToDB Framework
for Microsoft SQL Server

Views

ViewDescription
xls.queriesThis view selects database objects for the SaveToDB Connection Wizard and SaveToDB Query List.
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_primary_keysThis view is an Excel form for managing primary key constraints.
xls.view_unique_keysThis view is an Excel form for managing unique constraints.

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.view_all_translations

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

Source tables: xls.translations

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_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_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.

Contents

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.