There are many tools that can be utilized to troubleshoot data in Dynamics GP. One of the most powerful tools is scripting with SQL server. It should be noted that scripting with SQL server can manipulate data and should only be used by users who know how their scripts will affect Dynamics GP.
In this post, I want to cover a script that searches through a database or company for a particular field name. For example you may want to search for all tables in a company that store information in a field called CURNCYID. To accomplish this, utilize the following script:
— Find all tables in a SQL database that contain a particular field name.
— Edit WHERE syscol.name LIKE ‘[field name]’ to show which field.
— Edit USE [Company ID] to show which company.
USE two
SELECT systab.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
syscol.name AS column_name
FROM sys.tables AS systab
INNER JOIN sys.columns syscol ON systab.OBJECT_ID = syscol.OBJECT_ID
WHERE syscol.name LIKE ‘curncyid’
ORDER BY schema_name, table_name;
