Find Tables in SQL with Particular Fields

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 LIKE ‘[field name]’ to show which field.

— Edit USE [Company ID] to show which company.

USE two

SELECT AS table_name,

SCHEMA_NAME(schema_id) AS schema_name, AS column_name

FROM sys.tables AS systab

INNER JOIN sys.columns syscol ON systab.OBJECT_ID = syscol.OBJECT_ID

WHERE LIKE ‘curncyid’

ORDER BY schema_name, table_name;

Share this post

Related Posts

Checking Your CMMC Progress

Written by Alec Toloczko With Cybersecurity Maturity Model Certification (CMMC) requirements on the horizon, it’s crucial for organizations handling Controlled Unclassified Information (CUI) to adhere

Read More »