How-toSYSPROSQL / Reporting

SYSPRO tables list: core database tables by module and safe queries

Question
syspro tables list

Also searched as

  • syspro database tables
  • syspro table names inventory sales orders
  • syspro sql query examples

Short answer

SYSPRO stores data in SQL Server with one admin database (SysproAdmin) and one database per company (for example SysproCompanyA). Table names are module prefixed and Pascal case, such as InvMaster, SorMaster and WipMaster. Query them read-only and write only through business objects.

Applies to: SYSPRO 7 and SYSPRO 8 on SQL Server. Table and column names can change between releases, so confirm in your own database with the queries below.

Explore SYSPRO tables safely

  1. 1Connect with a read-only SQL login, ideally to a replica or restored copy.
  2. 2Pick the company database, not SysproAdmin, for business data.
  3. 3List tables by prefix to see a module: Inv, Sor, Wip, Por, Ar, Ap, Gen, Bom.
  4. 4List the columns of a table with INFORMATION_SCHEMA to confirm exact names.
  5. 5Use the key columns (StockCode, SalesOrder, Job, Customer, Supplier) to join tables.
  6. 6Build and test the query on a small slice with TOP before running it over everything.
  7. 7Save it as a view so reports and tools reuse one definition.

Naming conventions

Tables start with a module prefix: Inv for inventory, Sor for sales orders, Wip for work in progress, Por for purchase orders, Ar for receivables, Ap for payables, Gen for general ledger and Bom for bills of materials. Many transactional tables have a master and a detail, for example SorMaster for the header and SorDetail for lines.

Do not guess columns. Names such as StockCode, SalesOrder and Customer are common, but check them in your release. Customer defined fields and the way some modules store keys with padding differ by setup.

Core tables by module

These are widely used tables. Verify each in your database before depending on it.

Inventory   InvMaster (stock codes), InvWarehouse (qty by warehouse)
Sales       SorMaster (order header), SorDetail (order lines)
WIP         WipMaster (job header), WipJobAllMat (material allocations)
Purchasing  PorMasterHdr (PO header), PorMasterDetail (PO lines)
Receivables ArCustomer
Payables    ApSupplier, ApInvoice
GL          GenMaster (accounts), GenJournalDetail
BOM         BomStructure

Safe query patterns

Use NOLOCK or read committed snapshot on a replica for reporting so you do not block users, and accept that dirty reads are possible. Never update or delete in SYSPRO tables directly: stock, costing and GL are linked in ways the tables alone do not show.

For repeatable reporting, create views owned by a reporting schema and grant read access to those only. That gives you a stable contract when table details shift in an upgrade.

-- list tables for a module
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'Sor%' ORDER BY TABLE_NAME;
-- columns of a table
SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'InvMaster' ORDER BY ORDINAL_POSITION;
-- sample: open sales order lines (check column names first)
SELECT TOP 50 SalesOrder, StockCode, MOrderQty
FROM SorDetail WITH (NOLOCK);

Try it here, free

SYSPRO SQL Query Generator

Open full tool

Generate read-only SQL Server queries against common SYSPRO tables. Fill in database and filters, then copy the code.

Common pitfalls

  • !Writing to tables directly, which bypasses costing and GL.
  • !Running heavy reports against the live database during order entry.
  • !Assuming column names without checking, then silently joining on the wrong key.
  • !Querying SysproAdmin for business data.
  • !Building reports on tables that change in the next release instead of on a view.

How an ERP-grounded AI assistant handles this

ERPray can map a plain question such as open orders by customer to the right SYSPRO tables, show you the generated read-only SQL, and run it under a read-only login. Deployed on-prem, it keeps the schema and results inside your network.

See ERPray

Frequently asked questions

Where is the SYSPRO data stored?

In SQL Server: SysproAdmin holds operators, companies and system settings, and each company has its own database, commonly named with a SysproCompany prefix plus the company id.

Is there an official table list?

SYSPRO publishes data dictionary type information to partners and customers. The most reliable list for your release is the one in your own database, via INFORMATION_SCHEMA.

Can I add my own tables?

You can add custom tables and views in a separate schema, but keep them clear of SYSPRO's own objects so upgrades do not collide with them.

Why are some values stored as padded text?

Some keys such as sales orders are stored as fixed width text, often with leading zeros. Match that format when joining or filtering.

Related

Advanced

SYSPRO SQL Query for Inventory On Hand by Warehouse

SYSPRO runs on SQL Server with one database per company, typically named like SysproCompanyA, and inventory is split between InvMaster for part-level data and InvWarehouse for quantities per warehouse. Join them on StockCode, filter, and read the quantity columns. Use a read-only login, never write to SYSPRO tables directly, and prefer e.net business objects for any change.

Advanced

SYSPRO API integration: e.net business objects, REST, EDI and Office

SYSPRO integrates mainly through e.net Solutions, a set of business objects that take and return XML, exposed through COM, .NET and web service interfaces including a REST style service in recent SYSPRO 8 releases. EDI and Office integration cover document exchange and spreadsheet work. Pick the route by direction, volume and who owns the code.

How-to

SYSPRO costing methods: standard, average, FIFO and last cost

SYSPRO sets the costing method on each stock code, so one company can mix methods. Standard cost holds a fixed cost and posts variances, average cost blends receipts into a moving cost, FIFO values stock at the cost of the oldest layers, and last cost uses the latest purchase cost. The method drives inventory value, WIP and variance postings.

How-to

SYSPRO reporting API: e.net, SRS server-side reporting, Crystal Reports and SRS preview problems

SYSPRO gives you three reporting routes: the e.net Solutions business object API (XML in, XML out) for programmatic data, SYSPRO Reporting Services (SRS) for designed reports run on the server, and Crystal-based or SQL-based custom reports. If the SRS preview does not work, check the services, the runtime and bitness, the SQL permissions and the client and server versions in that order.

Error fix

Error validating SYSPRO online profile: what it means and how to fix it

A profile validation error means the SYSPRO client or service could not confirm the operator, company, licence or database connection it was handed at logon. Check the service and licence first, then the operator and company, then the database and disk, in that order. Storage errors are usually full disks, a locked SQL file or a tempdb problem rather than a SYSPRO bug.

How-to

How to update SYSPRO (and how to check the latest version)

SYSPRO 8 ships as named releases (for example 2022 R1 style names) plus later updates, and the current release is listed on the SYSPRO partner and customer portals, not on a fixed public page. To update, back up, test the new build on a copy of your data, then update the server, services and clients in a planned window.

AI for ERP

AI for SYSPRO, Grounded in Your Own Manufacturing Data

Add AI to SYSPRO without moving your manufacturing data to a public model. On-premise and private-cloud LLMs for SYSPRO in Australia, NA, and beyond.

Stuck on SYSPRO?

Talk to engineers who work inside SYSPRO every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

AI on your ERP

Integrations

Software