ERP Table and Field Explorer

SYSPRO ERP Database Tables and Field Explorer

Search the core SYSPRO 8 company database tables by table name, column or description, see each table's key, important columns and joins, then pick two to four tables to get a SQL Server JOIN skeleton. Each SYSPRO company has its own SQL Server database, so there is no company column to join on, and table names follow the module prefix: Sor for sales orders, Inv for inventory, Wip for jobs, Bom for structures and routings, Por for purchase orders, Ar, Ap and Gen for the ledgers.

34 tables, 224 documented fields and 65 join paths across 11 modules. Free, runs in your browser, no login needed.

1Find a table by name, description or field

Naming: Each SYSPRO company is a separate SQL Server database (in the customer SQL we checked, SysproCompanyA for company A); system-wide tables such as SysproAdmin, which lists the companies, are kept apart from the company tables. Table names start with the module: Sor sales orders, Inv inventory, Wip work in progress, Bom structures and routings, Por purchase orders, Mrp requirements planning, Ar, Ap and Gen for receivables, payables and the general ledger, Tbl for shared codes. Custom form fields sit in a table with a + suffix, for example InvMaster+.

Tables by module

2Pick tables

34 of 34

Inventory

InvMaster

Stock code master: one row per stock code with description, category, default warehouse, supplier, lead times and standard costs.

Also known as: Inventory Query, stock code, item master

Key:StockCode
FieldMeaning
StockCodeStock code.
DescriptionShort description.
LongDescLong description.
StockUomStocking unit of measure.
ProductClassProduct class, used for sales and GL integration.
PartCategoryPart category, for example B for a bought-out part.
SupplierDefault supplier (ApSupplier).
WarehouseToUseDefault warehouse for the stock code.
LeadTimeLead time in days.
MaterialCostMaterial cost element.
LabourCostLabor cost element.
DrawOfficeNumDrawing office number.

Joins

  • InvMaster.StockCode = InvWarehouse.StockCode

    One row per warehouse the stock code is stocked in.

  • InvMaster.Supplier = ApSupplier.Supplier
  • InvMaster.StockCode = BomStructure.ParentPart

    Components when this stock code is a parent.

  • InvMaster.StockCode = BomOperations.StockCode

    Routing operations of the parent.

  • InvMaster.StockCode = InvPrice.StockCode
  • InvMaster.StockCode = InvMovements.StockCode
  • InvMaster.StockCode = SorDetail.MStockCode
  • InvMaster.StockCode = PorMasterDetail.MStockCode
  • InvMaster.StockCode = WipMaster.StockCode
  • InvMaster.StockCode = WipJobAllMat.StockCode
  • InvMaster.StockCode = MrpDetailPegging.StockCode
  • InvMaster.StockCode = ArTrnDetail.StockCode

Also AlternateKey1, AlternateKey2, Planner, ManufLeadTime, DockToStock and CycleCount. Custom form fields for stock codes live in a separate table, InvMaster+, joined on StockCode. Columns added in recent releases include QmInspectionReq (2022), ArWithTaxCode (2025) and Brand, Designation, Formula and ProductFamily (2026).

3Generate a JOIN skeleton (2 to 4 tables)

Starters:

Dialect: SQL Server (T-SQL)

Runs against one SYSPRO company database on SQL Server. Inside SYSPRO, Business Activity Queries publish the same kind of join as a SQL view in each company database, usable from business insight tiles and the SYSPRO OData feed.

-- Query skeleton generated by netray.co/tools/table-explorer
-- Dialect: SQL Server (T-SQL). Review keys, filters and indexes before running on production.
-- Connect to the company database (for example SysproCompanyA); tables are in dbo and carry no company column.
-- Order, purchase order and invoice numbers are character columns with leading zeros: compare with the padded value, for example '000000000115674'.
-- Read only. Write through SYSPRO programs or business objects (e.net), not with direct UPDATE statements.

SELECT TOP 100
    sorm.SalesOrder,
    sorm.Customer,
    sorm.CustomerName,
    sorm.CustomerPoNumber,
    sorm.OrderStatus,
    sord.SalesOrderLine,
    sord.LineType,
    sord.MStockCode,
    sord.MStockDes,
    sord.MWarehouse,
    invw.StockCode,
    invw.Warehouse,
    invw.QtyOnHand,
    invw.QtyAllocated,
    invw.QtyAllocatedWip,
    invm.Description,
    invm.LongDesc,
    invm.StockUom,
    invm.ProductClass,
    invm.PartCategory
FROM dbo.SorMaster AS sorm
LEFT JOIN dbo.SorDetail AS sord
    ON sorm.SalesOrder = sord.SalesOrder
-- SorDetail to InvWarehouse: Merchandise lines only.
LEFT JOIN dbo.InvWarehouse AS invw
    ON sord.MStockCode = invw.StockCode
   AND sord.MWarehouse = invw.Warehouse
-- SorDetail to InvMaster: Merchandise lines only.
LEFT JOIN dbo.InvMaster AS invm
    ON sord.MStockCode = invm.StockCode
ORDER BY sorm.SalesOrder;
Free, optional. Reopen it on any device.
Download:

How to use it

  1. 1

    Search

    Type a table name such as SorDetail, a column such as MOrderQty, or a business word such as job or invoice. Field matches are highlighted under each table.

  2. 2

    Filter and inspect

    Narrow by module, then open a table to see its key, the columns that matter and the tables it joins to. Click a join to jump to that table.

  3. 3

    Build the join

    Add two to four tables with the plus button or a starter. The SQL skeleton uses the dbo schema; run it in SQL Server Management Studio against your company database.

  4. 4

    Keep it

    Star tables as favorites, export to PDF, Excel, Word or CSV, and optionally save the whole setup to your Netray account to reopen it later.

Questions about SYSPRO ERP tables

Which SYSPRO tables hold sales orders?

SorMaster is the order header (customer, order status, document type, dates, ship-to address) and SorDetail holds the lines keyed by SalesOrder and SalesOrderLine. Merchandise line columns start with M (MStockCode, MOrderQty, MShipQty, MBackOrderQty, MPrice) and non-merchandise columns with N (NComment). Bin allocations of open orders are in SorDetailBin and move to MdnDetailBin when the order is dispatched.

What do the SorMaster OrderStatus codes mean?

Per SYSPRO's Sales Orders help: 0 order in process, 1 open order, 2 open back order, 3 released back order, 4 in warehouse, 8 to invoice (or to transfer), 9 complete, F forward order, S in suspense (credit check), * cancelled during entry and \ cancelled.

Where is the bill of materials stored in SYSPRO?

BomStructure holds parent to component rows (ParentPart, Component, QtyPer, Route) and BomOperations holds the routing of the parent (StockCode, Route, Operation, WorkCentre). Work centers and machines are in BomWorkCentre and BomMachine. Route 0 is the standard route; other 2-character route codes are alternate routings.

How are jobs stored?

WipMaster is the job header with the stock code, QtyToMake, dates and the Complete flag. Materials allocated to the job are in WipJobAllMat (one row per Job and Line, with OperationOffset pointing to the operation) and operations in WipJobAllLab (Job and Operation). Postings against the job are in WipJobPost, where M prefix columns describe material issues and L prefix columns labor.

Why does WHERE SalesOrder = '115674' return nothing?

SYSPRO stores sales order, purchase order and invoice numbers as character columns with leading zeros, so order 115674 is stored as '000000000115674'. Compare with the padded value, for example RIGHT(REPLICATE('0', 15) + '115674', 15), which keeps the index usable, rather than wrapping the column in a function.

Is there a company column to filter on?

No. Each SYSPRO company has its own SQL Server database, so you connect to that company's database and query dbo tables directly. Some ledger tables such as GenControl and GenHistory carry a Company column, but most tables do not. SYSPRO lists the companies in the system-wide SysproAdmin table.

How do I get GL balances by period?

GenHistory holds BeginYearBalance per company, GL code and year, and GenTransaction holds posted lines with GlYear, GlPeriod, GlCode and EntryValue. A period end balance is the opening balance plus the sum of EntryValue up to that period. GenControl gives the current GL year and period and the period end dates (CurYrPrdEnd1 and on).

Can I update SYSPRO tables with SQL, or query them in SYSPRO Cloud?

Read with SQL, write through SYSPRO programs or business objects, which apply the posting, costing and audit logic that direct UPDATE statements skip. In SYSPRO Cloud ERP direct database access is restricted; SYSPRO points to SYSPRO OData for data access without SQL and to Business Activity Queries for published datasets.

Sources

Table names and columns were checked against these public references. Tables whose key or columns we could not confirm say so in their notes. Column sets change between releases, so confirm against the data dictionary of your own version before you build on it.