Standard tables and columns (HealthCheckSqlStdTablesColumns.sql)

This script checks the standard SYSPRO tables and columns for discrepancies.

Note that the following standard SYSPRO tables are excluded from any checks as there is no definition for these in the data dictionary:

Standard tables

This checks the list of tables defined in the data dictionary against the tables in SQL to identify missing standard tables.

User defined tables

This checks the list of tables defined in SQL and compares it with those in the data dictionary. Anything not in the data dictionary is seen as a user defined table.

Custom form tables

Checks the custom form tables (those in the database with a plus “+” suffix are seen as custom form tables) and columns for discrepancies.

Standard custom form table

When you associate custom form information against an entity such as Stock codes, then an entry is written into the custom form control table. Checks are made to find entries in the custom form control table that do not exist in the database.

User defined custom form table

Any table found in the database with a “+” suffix that does not exist in the custom form control is seen as a user defined custom form table.

Standard SYSPRO table keys and indexes (HealthCheckSqlStdIndexes.sql)

This script checks the indexes on all standard SYSPRO tables and compares them to their definitions in the data dictionary.

Standard indexes

When a standard SYSPRO index does not exist on a table and it is the primary key, then this can allow duplicates to be written to the table which can lead to serious data corruption. Also, missing indexes can slow processing down as SQL reverts to a table scan when reading.

User defined indexes

A number of customers may add user defined indexes to a standard SYSPRO table to improve performance on customised reports, etc.

Standard columns

The columns for standard tables are compared between what is in the data dictionary and in SQL itself. Any columns in the data dictionary, but not in SQL are considered missing.

Note that all the standard tables have TimeStamp columns and whilst these are not defined in the data dictionary, they are checked to see whether they exist.

User defined columns

A user defined column is one which is defined on a standard SYSPRO table which does not exist in the data dictionary.

All user defined columns that are defined on a standard SYSPRO table must be set to either be NULLABLE or have a default value. If they do not, then this can cause failures inserting rows into the table using the standard file handlers as SYSPRO does not know of the existence of the user defined columns.

Column definitions vs SYSPRO definitions

Checks are made between what SYSPRO is expecting the column to be defined as and how it is actually defined in SQL. Checks are made on the following column attributes and any differences found are reported with the Column definition does not match SYSPRO definition message:

Custom form column definition vs SYSPRO definition

Currently only the NULLABLE property is checked. Any key column cannot be nullable whereas all other columns must be set to nullable. Other checks, such as datatypes and collation, may be introduced at a later point.

Standard custom form columns

Checks that any column that is defined in the custom form definition tables exist in the database. This checks both the primary ‘key’ columns (which link the custom form to the entity) as well as columns that are defined by the user.

User defined custom form columns

Checks that columns defined on a custom form table are in the custom form definition.

All user defined columns that are defined on a custom form table must be set to either be NULLABLE or have a default value. If they do not, then this can cause failures when SYSPRO processes these tables.

Standard SYSPRO table keys and indexes (HealthCheckSqlStdIndexes.sql)

This script checks the indexes on all standard SYSPRO tables and compares them to their definitions in the data dictionary.

When a standard SYSPRO index does not exist on a table and it is the primary key, then this can allow duplicates to be written to the table which can lead to serious data corruption. Also missing indexes can slow processing down as SQL reverts to a table scan when reading.

Index column mismatch

A check is made to ensure that all the columns that make up an index match the definition in the data dictionary. This includes columns missing from an index, columns in the incorrect sequence, or extra columns added to a standard SYSPRO index.

Index defined against incorrect table

A check is made to ensure indexes which should be on one table, are not found to exist on another table. This usually occurs when the original master table is renamed in SQL. When a table is renamed then all the constraints, defaults, etc. remain. This will cause problems when trying to create the original table.

User defined index

Customers may add user defined indexes to a standard SYSPRO table to improve performance on customised reports, etc.

User defined primary key

A check is made to ensure that a table has only a single primary key and conforms to the data dictionary definition in naming terms. A primary key on a standard SYSPRO table is always named as {tablename}Key.

Custom form table indexes (HealthCheckSqlCustomIndexes.sql)

All standard custom form tables have a primary key that links the table back to the original source document (for example InvMaster+ will have a key of InvMaster+Key which contains the ‘StockCode’ column).

A number of customers may add user defined indexes to a custom form table to improve performance on customised reports, etc.

This script checks for the following:

Foreign key checks (HealthCheckSqlFkeys.sql)

Foreign keys in SYSPRO are purely referential and are there to provide the ability to diagram the links between tables. They are not enforced, any enforcement of the foreign key rules are controlled programmatically within SYSPRO.

Checks are made for the following: