An Introduction to the SYSPRO SQL Database Structure
In the early versions of SYSPRO, the ERP’s data was entirely stored in C-ISAM files. These C-ISAM files were essentially text files containing system data. They could cause a lot of trouble if you had to investigate or troubleshoot data problems as they were lacking a stable framework to review and analyze them. Thankfully, a modern SQL database structure was introduced between SYSPRO 6.1 and 7. In today’s SYSPRO 8 version, essentially all components of the ERP have now been converted and are stored in traditional SQL database tables. Knowing what SYSPRO’s database looks like becomes a vital tool if you are tasked to troubleshoot data problems, analyze company data, or build SQL queries to pull data for custom reports.
So what does a SYSPRO database structure look like?
The “System-Wide” Database
Both SYSPRO 7 and SYSPRO 8 contain what SYSPRO calls the “system-wide” database. While the name of the database can be customized to the business’ liking, it is commonly named “sysprodb” by default. The database contains information about operators, administrative settings, system information, and licensing details. Any data pertaining to system-wide configurations that span all SYSPRO companies will be found in this database. Particularly important tables to take note of in this database are:
- AdmOperator – Contains data of all operators on file in SYSPRO along with their role, group, and other access level details.
- AdmSysVersion – Header table containing the database version details of the SYSPRO environment.
- SysproAdmin – Table containing a list of the companies in your SYSPRO environment. This table becomes relevant when creating test companies or setting up SYSPRO’s default test databases.
The Company Databases
Each company created in SYSPRO will have its own database and default company tables. By default, SYSPRO names these companies ‘SysproCompany’ along with the company’s ID at the end. Ex, SysproCompanyT, SysproCompanyA, etc. These are the databases where your company’s transactional data is stored.
At a first glance, these databases can be overwhelming. A standard SYSPRO database can have over 1000 tables in it. The good thing is that while this full list of tables compromises all of SYSPRO’s functionality, it is unlikely that your environment is both licensed and actively using SYSPRO’s full suite of products. This means that most of these tables will be empty and will not be worth much of your attention.
Instead, there are about 25 – 50 tables in any given environment that sees most of the action. To provide a cheat sheet, here are some of the commonly used tables in SYSPRO that are essential to the daily use of SYSPRO:
- ApInvoice – Supplier invoices
- ApSupplier – Supplier header details
- ApSupplierAddr – Addresses for the suppliers
- ArCustomer – Customer header details
- ArInvoice – Customer invoices
- ArTrnDetail – Details about each invoice. Used extensively for sales reporting
- BomOperations – Operations against a BOM
- BomStructure – Structure against a BOM
- InvMaster – Master for all stock codes in SYSPRO
- InvMovements – Inventory transaction details against each SKU
- InvPrice – Sell prices against each SKU
- InvWarehouse – Master table for inventory counts of each SKU
- PorMasterHdr – Purchase order header
- PorMasterDetail – Purchase order details
- SorMaster – Sales order master
- SorDetail – Sales order details
- WipMaster – Master for jobs
- WipJobAllLab – Labor details against jobs
- WipJobAllMat – Material details against jobs
- WipJobPost – History of material/labor issued to jobs
The “Deployment” Database
Unique to SYSPRO 8, this database handles information relating to the release version of your SYSPRO environment. The “SYSPRO 8 Installer” tool uses this database exclusively to verify which products are available to you and whether any recent updates have become available. In addition, it contains information about which machines have SYSPRO clients installed on them and which versions they have installed.
Admins, do you grasp the SYSPRO database structure for efficient data troubleshooting and analysis?
This covers the databases you will see in a usual SYSPRO environment. Knowing what SYSPRO’s database structure looks like is essential for any SYSPRO admin to effectively be able to find, troubleshoot, and analyze vital company data.
