Clear Cutover Get a quote

Data and SQL

SyteLine SQL table cheat sheet: _mst vs _mst_all, site_ref, and the tables behind orders, items and jobs

By Al Hasib Tamim, published 23 October 2026

Applies to: SyteLine 8, 9 and 10 on-premises (SQL Server). Not available in multi-tenant cloud. See the note at the end.

Short answer

SyteLine stores its data in SQL Server tables with an _mst suffix, such as item_mst, co_mst and coitem_mst. Every row carries a site_ref column that says which site owns it. The same names without the suffix (item, co) are views filtered to the site your session is in, which is why they can come back empty in SQL Server Management Studio. Tables ending in _mst_all hold data replicated from other sites in a multi-site setup. To write reliable queries, use the _mst tables, filter on site_ref, and join on both the business key and site_ref.

The three naming patterns

Pattern Example What it is When to use it
name_mst item_mst The base table. Every row has a site_ref. Reporting and analysis queries, filtering on site_ref yourself
name (no suffix) item A view over _mst, filtered to the current session's site Inside SyteLine code, where the site context is already set
name_mst_all co_mst_all Data replicated across sites in a multi-site setup Cross-site reporting, when replication is configured

Why does SELECT * FROM item return nothing in SSMS? Your SSMS session has no SyteLine site context, so the site-filtered view has nothing to match. Either query item_mst with WHERE site_ref = 'YOURSITE', or set the site context for your session with the SetSiteSp procedure. Check its parameters in your own database first.

Columns you'll see on almost every table

  • site_ref: the owning site. Always filter on it, and always include it in joins.
  • RowPointer: a unique identifier for each row. Notes and other attachments link back to records through it.
  • CreatedBy, UpdatedBy, CreateDate, RecordDate: audit columns. RecordDate is the last-modified date.
  • NoteExistsFlag: tells you whether the record has notes attached.

The core tables by area

Sales and customers

Table Holds
customer_mst Customer settings (terms, credit, salesperson, etc.)
custaddr_mst Customer names and addresses. cust_seq = 0 is the main customer record, and higher numbers are ship-tos.
co_mst Customer order headers
coitem_mst Customer order lines (one row per line and release)
co_ship_mst Customer order shipments
inv_hdr_mst / inv_item_mst Invoice headers and lines
artran_mst Accounts receivable transactions (invoices, credits, payments)

A common trap: the customer's name isn't in customer_mst. It's in custaddr_mst, in the row with cust_seq = 0.

Purchasing and vendors

Table Holds
vendor_mst Vendor settings
vendaddr_mst Vendor names and addresses
po_mst Purchase order headers
poitem_mst Purchase order lines

Items and inventory

Table Holds
item_mst Item master
itemwhse_mst Item quantities and settings per warehouse
itemloc_mst Item quantities per stock location
lot_mst Lots
whse_mst / location_mst Warehouses and locations
matltran_mst Material transactions: every inventory movement, with a trans_type code
prodcode_mst Product codes

Production

Table Holds
job_mst Jobs (job orders)
jobroute_mst Job operations (routing)
jobmatl_mst Job materials (bill of material on the job)
jobtran_mst Job labor and machine transactions
wc_mst Work centers

Finance

Table Holds
chart_mst Chart of accounts
ledger_mst General ledger postings
currency_mst Currency codes

Finding the table behind any form

You don't need to guess. Open the form in Design Mode and note the IDO its main collection uses. Then open the IDOs form, find that IDO, and look at its tables. That shows you exactly which _mst tables, and which joins, feed the form. This is the fastest way to answer "which table is this field in?"

Three starter queries

These are read-only examples written for this article. Run them against a test copy first, and replace 'MAIN' with your site name.

Open customer order lines with the customer name

SELECT  co.co_num,
        ca.name            AS customer_name,
        ci.co_line,
        ci.item,
        ci.qty_ordered,
        ci.qty_shipped,
        ci.due_date
FROM    co_mst      AS co
JOIN    coitem_mst  AS ci ON ci.co_num  = co.co_num
                         AND ci.site_ref = co.site_ref
JOIN    custaddr_mst AS ca ON ca.cust_num = co.cust_num
                         AND ca.cust_seq = 0
                         AND ca.site_ref = co.site_ref
WHERE   co.site_ref = 'MAIN'
  AND   ci.stat = 'O'          -- open (ordered) lines
ORDER BY ci.due_date;

On-hand quantity by warehouse

SELECT  iw.item,
        i.description,
        iw.whse,
        iw.qty_on_hand
FROM    itemwhse_mst AS iw
JOIN    item_mst     AS i ON i.item = iw.item
                         AND i.site_ref = iw.site_ref
WHERE   iw.site_ref = 'MAIN'
  AND   iw.qty_on_hand <> 0
ORDER BY iw.item, iw.whse;

Open purchase order lines with the vendor name

SELECT  po.po_num,
        va.name             AS vendor_name,
        pi.po_line,
        pi.item,
        pi.qty_ordered,
        pi.qty_received,
        pi.due_date
FROM    po_mst      AS po
JOIN    poitem_mst  AS pi ON pi.po_num  = po.po_num
                         AND pi.site_ref = po.site_ref
JOIN    vendaddr_mst AS va ON va.vend_num = po.vend_num
                         AND va.site_ref = po.site_ref
WHERE   po.site_ref = 'MAIN'
  AND   pi.stat = 'O'
ORDER BY pi.due_date;

Rules that keep you out of trouble

  • Read, don't write. Never UPDATE or INSERT into _mst tables directly. SyteLine's business logic lives in its IDOs, stored procedures and triggers, so a direct write skips all of it. That's how balances drift and orders end up in states the application can't fix. Make changes through the application, a utility, or an IDO call.
  • Use a read-only login for reporting tools.
  • Always join on site_ref as well as the key. Leaving it out works fine in a single-site database, then silently doubles your rows the day a second site is added.
  • Check status codes against your own data before relying on them in filters.

A note on multi-tenant cloud

In multi-tenant CloudSuite Industrial you have no direct SQL access. The same data is reached through IDOs, which means DataViews, the IDO Runtime, and the REST API. The table knowledge above still helps there, because IDO properties map closely to these columns.

FAQ

What does _mst mean in SyteLine table names?

_mst marks the base table that holds the data for every site, with a site_ref column showing which site owns each row.

Why does my SyteLine view return no rows in SQL Server Management Studio?

Views without the _mst suffix filter to the current session's site, and an SSMS session has none. Query the _mst table with a site_ref filter, or set the site context first.

Where is the customer name stored in SyteLine?

In custaddr_mst, in the row for that customer with cust_seq = 0.

Can I update SyteLine tables directly with SQL?

You shouldn't. Direct writes bypass SyteLine's business logic and can corrupt related records. Use the application, a utility, or an IDO method.

Systems I work on