Sooner or later, every Dynamics GP user meets a question that the standard screens cannot answer. An auditor wants every payment made to one vendor across ten years. A controller needs to know why a subledger total does not agree with the General Ledger. An IT manager is asked to feed GP data into a new reporting tool. At that point, someone has to look beneath the windows and into the SQL Server tables where GP actually keeps its data.
This can feel intimidating at first. GP has thousands of tables, and their names look like a code nobody explained. The good news is that the code is quite logical once you learn to recognize the pattern. Here, I will walk you through how GP tables are named, which ones finance and IT teams use most often, and how to work with them safely. This guidance comes from the daily experience of our Dynamics GP consultants at Nevas Technologies, who query these tables for clients every week.
Two Kinds of Database
Before looking at individual tables, it helps to know where they live. A GP installation has one system database, usually called DYNAMICS, and a separate company database for each company your organization runs in GP.
The system database holds information shared across all companies: users, company records, security, and the activity of who is logged in. The company databases hold the business data itself: accounts, vendors, customers, items, transactions and history. When you query financial data, you will almost always be working in a company database. When you are dealing with users or locks, you will be in DYNAMICS.
How to Read a GP Table Name
Most GP table names follow the same shape: a module prefix followed by five digits. Once you recognize the two parts, you can usually guess what a table holds before you open it.
The prefix tells you the module. Some of the most common are:
- GL – General Ledger
- PM – Payables Management
- RM – Receivables Management
- SOP – Sales Order Processing
- POP – Purchase Order Processing
- IV – Inventory
- FA – Fixed Assets
- UPR – US Payroll
- SY – System and company setup
The first digit tells you the type of data. This is the part most people find useful:
- 0 – master records, such as accounts, vendors, customers and items
- 1 – work tables, holding transactions that are entered but not yet posted
- 2 – open tables, holding posted transactions that are still active
- 3 – history tables, holding completed or fully applied transactions
- 4 – setup tables, holding module settings
So PM00200 is a Payables master table, which is where vendor records live. PM20000 holds open, posted Payables transactions. PM30200 holds Payables history. The same logic applies throughout the system, and it makes analyzing an unfamiliar problem much quicker.
One point to emphasize: a transaction moves between these tables as it progresses. An invoice starts in a work table, moves to an open table when posted, and moves to history when it is paid in full. If you search only one stage, you will miss records. Many reporting errors come from exactly this oversight.
The Tables Finance Teams Use Most
Below is a practical shortlist. It is not complete, but it covers the questions we are asked most often.
General Ledger
- GL00100 – Account Master. One row per account, with its description, type and active status.
- GL00105 – Account Index Master. Links the internal account index to the full account number as users see it.
- GL10000 / GL10001 – Journal entries entered but not yet posted (header and lines).
- GL20000 – Posted journal lines for open fiscal years.
- GL30000 – Posted journal lines for years that have been closed.
- GL10110 / GL10111 – Period summary balances for open and historical years.
A common mistake is reporting from GL20000 alone and wondering why prior-year detail is missing. After a year-end close, that detail moves to GL30000.
Payables Management
- PM00200 – Vendor Master.
- PM10000 – Unposted Payables transactions.
- PM20000 – Posted transactions that are still open.
- PM30200 – Paid transaction history.
- PM10200 / PM30300 – Apply records showing which payments settled which invoices.
Receivables Management
- RM00101 – Customer Master.
- RM00102 – Customer address records.
- RM10301 – Unposted sales transactions entered in Receivables.
- RM20101 – Posted open transactions.
- RM30101 – Transaction history.
- RM20201 / RM30201 – Apply records for open and historical transactions.
Sales and Purchasing
- SOP10100 / SOP10200 – Sales documents in progress (header and lines).
- SOP30200 / SOP30300 – Posted sales history (header and lines).
- POP10100 / POP10110 – Open purchase orders (header and lines).
- POP30300 / POP30310 – Posted receipts history (header and lines).
Inventory and Fixed Assets
- IV00101 – Item Master.
- IV00102 – Item quantities by site.
- IV30200 / IV30300 – Inventory transaction history (header and lines).
- FA00100 – Asset general information.
- FA00200 – Asset book details.
- FA01400 – Fixed Asset account groups.
The Tables IT Teams Use Most
IT teams tend to work with a different set, usually because something is stuck.
- SY01400 (DYNAMICS) – User Master, listing GP user IDs.
- SY01500 (DYNAMICS) – Company Master, listing each company and its database name.
- SY60100 (DYNAMICS) – Which users can open which companies.
- ACTIVITY (DYNAMICS) – Users currently logged in.
- SY00800 (DYNAMICS) – Batches currently in use or being posted.
- SY00801 (DYNAMICS) – Resources currently locked by users.
- SY00500 (company) – Batch headers, including batch status.
- DEX_LOCK / DEX_SESSION (tempdb) – Low-level session and record locks.
When a user’s session drops during posting, records in these tables can be left behind and block others. Clearing them is a routine task for experienced support staff, but it must be done carefully. Removing the wrong record while another user is genuinely working can damage data. Our Dynamics GP support team handles these cases regularly for clients.
Finding a Table You Do Not Recognize
You do not need to memorize table names. GP gives you a way to look them up.
Resource Descriptions. Inside GP, go to Tools, then Resource Descriptions, then Tables. You can search by the friendly name, such as “Vendor Master”, and see the physical table name and every field it contains. This is the most reliable reference, because it reflects your exact version and installed products.
Find the window, then the table. If you know which window holds the data, you can identify the tables it uses through tools such as GP Power Tools, a separately licensed add-on popular with GP administrators.
Third-party and custom tables. Products from independent vendors add their own tables, usually with their own prefixes. Any customization built for your company may also store data in custom tables. Keep a written record of these, because they are easy to overlook during upgrades and migrations.
Rules for Working with GP Tables Safely
Direct access to the database is powerful, and that is exactly why it needs discipline.
Read freely; write rarely. Running SELECT queries to report or investigate is generally safe. Writing to GP tables directly is a different matter. GP’s own windows apply business rules, update related tables and keep subledgers in balance. A direct UPDATE or DELETE skips all of that and can leave data inconsistent in ways that surface months later.
Always back up first. If a correction truly must be made in SQL, take a full backup, test the script in a copy of the company, and only then run it in live data.
Use the right reporting route. For regular reporting, SmartList, SmartList Designer, Excel reports and SQL Server Reporting Services are safer than ad hoc queries. Each respects GP’s structure and security settings.
Respect security and licensing. Giving staff direct SQL access bypasses GP’s user security, so it should be limited to people who genuinely need it. Also check that any external tool reading GP data complies with your Microsoft license terms and your own data policies.
Mind the dates and decimals. GP stores some dates with default values such as 1 January 1900 for “no date”, and stores amounts to five decimal places. Reports that ignore these details can show strange results.
Why This Knowledge Matters More Now
With Microsoft ending Dynamics GP support at the end of 2029, many companies are preparing to move to a new platform. A clear understanding of GP tables helps in several ways: reconciling subledgers before migration, extracting history accurately, documenting custom tables, and confirming that totals in the new system match the old.
If you are not yet on the latest release, an upgrade to the current version of Dynamics GP gives you the longest period of Microsoft support and a cleaner database to work with when the time comes to move.
Summarizing the Essentials
Summarizing the key points for your team:
- GP has one system database (DYNAMICS) and one database per company.
- Table names combine a module prefix with a five-digit number.
- The first digit shows the data type: master, work, open, history or setup.
- Transactions move from work to open to history, so search all stages.
- Use Resource Descriptions to find any table and its fields.
- Query freely, but avoid writing to tables directly without backup and testing.
- Document any third-party or custom tables your company relies on.
Keep this list with your internal procedures, and new team members will find their way around GP data far more quickly.
How Nevas Technologies Can Help
For more than a decade, Nevas Technologies has helped companies across the United States and Asia Pacific make the most of Microsoft Dynamics GP. Our consultants write reports, investigate data issues, clear stuck batches, document customizations and prepare GP data for migration.
If your finance or IT team needs help finding, understanding or correcting data in Dynamics GP, contact us. We can resolve the immediate issue and leave your team better equipped for the next one.
Frequently Asked Questions
Where are Dynamics GP tables stored? GP data is stored in Microsoft SQL Server. Each installation has one system database, usually called DYNAMICS, for users, companies and security, plus a separate database for each company, which holds accounts, vendors, customers, items and transactions.
How are Dynamics GP table names structured? Most names combine a module prefix, such as GL, PM or RM, with a five-digit number. The first digit shows the type of data: 0 for master records, 1 for unposted work, 2 for open posted transactions, 3 for history and 4 for setup.
Which table holds vendor records in Dynamics GP? Vendor records are held in PM00200, the Vendor Master table. Open posted Payables transactions are in PM20000, and paid transaction history is in PM30200.
Why is prior-year General Ledger detail missing from my report? Posted journal detail for open years is held in GL20000. After a fiscal year is closed, that detail moves to GL30000. Reports that need several years of history must read both tables.
How can I find which table a Dynamics GP field is stored in? In GP, go to Tools, then Resource Descriptions, then Tables. You can search by the table’s display name and see its physical name and all of its fields for your exact version and installed products.
Is it safe to update Dynamics GP tables directly in SQL? Reading data with queries is generally safe. Writing directly to tables is risky, because it bypasses the business rules GP applies through its own windows. Any correction made in SQL should only follow a full backup and testing in a copy of the company database.
Can Nevas Technologies help with Dynamics GP data and reporting? Yes. Our consultants write reports, investigate data issues, clear stuck batches and locks, document custom tables and prepare GP data for migration to a new platform.
