SaveToDB Framework
for Microsoft SQL Server
Views
| View | Description |
|---|---|
| xls.queries | This view selects database objects for the SaveToDB Connection Wizard and SaveToDB Query List. |
| xls.view_all_translations | This view is an Excel form for editing translation for all objects, columns, and parameters. |
| xls.view_developer_handlers | This view selects the handler configuration for generating views, procedures, and constraints. |
| xls.view_foreign_keys | This view is an Excel form for managing foreign key constraints. |
| xls.view_primary_keys | This view is an Excel form for managing primary key constraints. |
| xls.view_unique_keys | This 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
| Column | DataType | Comment |
|---|---|---|
| TABLE_SCHEMA | nvarchar(128) | sys.schemas.name, xls.objects.TABLE_SCHEMA |
| TABLE_NAME | nvarchar(128) | sys.objects.name, xls.objects.TABLE_NAME |
| TABLE_TYPE | nvarchar(128) | sys.objects.type, xls.objects.TABLE_TYPE |
| TABLE_CODE | nvarchar(max) | xls.objects.TABLE_CODE |
| INSERT_PROCEDURE | nvarchar(max) | xls.objects.INSERT_OBJECT |
| UPDATE_PROCEDURE | nvarchar(max) | xls.objects.UPDATE_OBJECT |
| DELETE_PROCEDURE | nvarchar(max) | xls.objects.DELETE_OBJECT |
| PROCEDURE_TYPE | nvarchar(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
| Column | DataType | Comment |
|---|---|---|
| SECTION | int | 1 - 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_ORDER | bigint | Sort order within a section |
| TRANSLATION_TYPE | varchar(9) | object, schema, column, or parameter |
| TABLE_TYPE | nvarchar(20) | table, view, function, or procedure |
| TABLE_SCHEMA | nvarchar(128) | xls.translations.TABLE_SCHEMA |
| TABLE_NAME | nvarchar(128) | xls.translations.TABLE_NAME |
| COLUMN_NAME | nvarchar(128) | xls.translations.COLUMN_NAME |
| LANGUAGE_NAME | varchar(2) | xls.translations.LANGUAGE_NAME |
| TRANSLATED_NAME | nvarchar(128) | xls.translations.TRANSLATED_NAME |
| TRANSLATED_DESC | nvarchar(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.
| Column | DataType | Comment |
|---|---|---|
| TABLE_SCHEMA | nvarchar(128) | Configured object schema |
| TABLE_NAME | nvarchar(128) | Configured object name |
| COLUMN_NAME | nvarchar(128) | Column or parameter name of the configured object |
| EVENT_NAME | varchar(7) | Actions |
| HANDLER_SCHEMA | varchar(3) | xls |
| HANDLER_NAME | varchar(37) | Handler object name |
| HANDLER_TYPE | varchar(13) | CODE or MENUSEPARATOR |
| HANDLER_CODE | nvarchar(max) | SQL code of the handler object |
| TARGET_WORKSHEET | nvarchar(128) | NULL |
| MENU_ORDER | int | Handler order in the Actions menu (200-220) |
| EDIT_PARAMETERS | bit | 1 - 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.
| Column | DataType | Comment |
|---|---|---|
| SORT_ORDER | bigint | Sort order |
| SCHEMA | nvarchar(128) | Table schema |
| TABLE | nvarchar(128) | Table name |
| COLUMN | nvarchar(128) | Column name |
| POSITION | int | Ordinal position |
| REFERENTIAL_SCHEMA | nvarchar(128) | Referenced table schema |
| REFERENTIAL_TABLE | nvarchar(128) | Referenced table name |
| REFERENTIAL_COLUMN | nvarchar(128) | Referenced column name |
| CONSTRAINT | nvarchar(128) | Constraint name |
| ON_UPDATE | nvarchar(60) | CASCADE, SET NULL, SET DEFAULT, OR NULL (NO_ACTION) |
| ON_DELETE | nvarchar(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.
| Column | DataType | Comment |
|---|---|---|
| SORT_ORDER | bigint | Sort order |
| SCHEMA | nvarchar(128) | Table schema |
| TABLE | nvarchar(128) | Table name |
| COLUMN | nvarchar(128) | Column name |
| POSITION | int | Ordinal position |
| CONSTRAINT | nvarchar(128) | Constraint name |
| INDEX_POSITION | int | Column position in the index |
| IS_DESCENDING | bit | NULL - 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.
| Column | DataType | Comment |
|---|---|---|
| SORT_ORDER | bigint | Sort order |
| SCHEMA | nvarchar(128) | Table schema |
| TABLE | nvarchar(128) | Table name |
| COLUMN | nvarchar(128) | Column name |
| POSITION | int | Ordinal position |
| CONSTRAINT | nvarchar(128) | Constraint name |
| INDEX_POSITION | int | Column position in the index |
| IS_DESCENDING | bit | NULL - ASC
1 - DESC |
| IS_INCLUDED | bit | NULL - 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.