#MSDynGP Checking which Products have been used in Dynamics GP

This article is designed to help you perform a system audit on your Microsoft Dynamics GP system and is used as part of my Microsoft Dynamics GP Systems Audit session in the first week of the Dynamics GP Strategic Planning Digital Summit being held each Wednesday for the next 5 weeks.

Identifying the installed products in the system is fairly easy as you can look at the Dynamics.set launch file to see all the Dexterity product dictionaries loaded.

However, having a product loaded in a system does not necessarily mean that it has been used.

The SQL script below is based on a list of products and tables provided Terry Heley by Microsoft Support and includes the record counts for any of the primary tables used in each product so you can see if a module is used.

Run the script against each company database to check which products have been used in that company:

/**********************************************************/
/*                                                        */
/* Script to check products used in Microsoft Dynamics GP */
/*                                                        */
/*  by David Musgrave, Winthrop Development Consultants   */
/*                                                        */
/*    Created: 05-Jul-2025, Last Modified: 05-Jul-2025    */
/*                                                        */
/**********************************************************/

set nocount on

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = OBJECT_ID('tempdb.dbo.#ProductTables') )
	DROP TABLE #ProductTables

CREATE TABLE #ProductTables (
	Product_Name varchar(100)
	,Table_Name varchar(20)
	,Table_Description varchar(100)
	)

-- Build Product Knowledge Base
insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('General Ledger (GL)', 'GL00100', 'Account Master')
																			  , ('General Ledger (GL)', 'GL00105', 'Account Index Master')
																			  , ('General Ledger (GL)', 'GL00102', 'Account Category Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Receivables Management (RM)', 'RM00101', 'Customer Master')
																			  , ('Receivables Management (RM)', 'RM00102', 'Customer Address Master')
																			  , ('Receivables Management (RM)', 'RM00103', 'Customer Master Summary')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Payables Management (PM)', 'PM00200', 'Vendor Master')
																			  , ('Payables Management (PM)', 'PM00300', 'Vendor Address Master')
																			  , ('Payables Management (PM)', 'PM00201', 'Vendor Electronic Funds Transfer (EFT) Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Inventory (IV)', 'IV00101', 'Item Master')
																			  , ('Inventory (IV)', 'IV00102', 'Item Quantity Master')
																			  , ('Inventory (IV)', 'IV00103', 'Item Site Master')
																			  , ('Inventory (IV)', 'IV00105', 'Item Price List')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Sales Order Processing (SOP)', 'SOP00100', 'Customer Item Master')
																			  , ('Sales Order Processing (SOP)', 'SOP10100', 'Sales Transaction Work Header')
																			  , ('Sales Order Processing (SOP)', 'SOP30100', 'Sales Transaction History Header')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Purchase Order Processing (POP)', 'POP00100', 'Vendor Item Master')
																			  , ('Purchase Order Processing (POP)', 'POP10100', 'Purchase Order Work Header')
																			  , ('Purchase Order Processing (POP)', 'POP30100', 'Purchase Order History Header')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Payroll (U.S.)', 'UPR00100', 'Employee Master')
																			  , ('Payroll (U.S.)', 'UPR00102', 'Employee Deduction Master')
																			  , ('Payroll (U.S.)', 'UPR00400', 'Pay Code Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Bank Reconciliation (CM)', 'CM00100', 'Checkbook Master')
																			  , ('Bank Reconciliation (CM)', 'CM00101', 'Checkbook EFT Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Fixed Assets (FA)', 'FA00100', 'Asset General Information')
																			  , ('Fixed Assets (FA)', 'FA00200', 'Asset Book Information')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Project Accounting (PA)', 'PA01201', 'Project Master')
																			  , ('Project Accounting (PA)', 'PA01101', 'Contract Master')
																			  , ('Project Accounting (PA)', 'PA01001', 'Cost Category Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Field Service (SVC)', 'SVC00200', 'Service Call Master')
																			  , ('Field Service (SVC)', 'SVC00100', 'Technician Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Manufacturing (MAN)', 'BM010415', 'BOM Revision Master')
																			  , ('Manufacturing (MAN)', 'RT010001', 'Routing Master')
																			  , ('Manufacturing (MAN)', 'CT00001', 'Costing Company Defaults')
																			  , ('Manufacturing (MAN)', 'WC010015', 'Work Center Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Human Resources (HR)', 'BE020230', 'Benefits')
																			  , ('Human Resources (HR)', 'TATX1030', 'Accrue Attendance')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Interfund Management (IF)', 'IF000001', 'Interfund Method Setup')
																			  , ('Interfund Management (IF)', 'IF000002', 'Interfund Account Setup')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Revenue Expense Deferral (RED)', 'PP000001', 'Deferral Setup')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Safe Pay (SP)', 'ME123501', 'Safe Pay Bank Upload Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Payment document Management (PDM)', 'RVLPD001', 'Company Payment Document Setup')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Analytical Accounting (AA)', 'AAG00100', 'Analytical Accounting System Setup')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Grant Management (GM)', 'GTM01100', 'Grant Master')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Encumbrance Management (EC)', 'ENC10300', 'Encumbrance Account Budget')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('VAT Daybook (VAT)', 'EDCVAT01', 'VAT Daybook Use')

insert into #ProductTables (Product_Name, Table_Name, Table_Description) values ('Canadian Payroll (CP)', 'CPY10100', 'Canadian Payroll Employee Master')


-- Check if any records in tables
select t.*, indx.rowcnt as Rows
from #ProductTables t
inner join sys.sysobjects objs on t.Table_Name = objs.name
INNER JOIN sys.sysindexes indx ON (objs.id = indx.id) AND (indx.indid < 2)
WHERE (objs.type = 'U') 
and indx.rowcnt > 0

DROP TABLE #ProductTables

/* End of Script */

If you have any products and tables you want to add to this script, please provide the Product Name, table name and table description and we can add it to the insert statements that build the product knowledge base. I am happy to add other Microsoft and ISV (Independent Software Vendor) products to the script.

Downloading and Installing

If you have GP Power Tools, you can use the following project to run the script:

Download the example code, import using the Project Setup window (without any project showing, select the path to the xml file and click Import):

• GP Power Tools Settings Gp Products In Use.dbg.zip

Once the project is loaded, just execute the GP PRODUCTS IN USE SQL Execute script.

Let me know if this is helpful.

David

01-Sep-2025: Added Script as a GP Power Tools project.

This article was originally posted on https://www.winthropdc.com/blog.