How we can find the foreign key relationships in GP system as well as company tables

(0) Share

Report

Posted on by MelvinGP

I'm doing a export of GP tables using the ETL tool Talend Open Studio. I need a filter column (eg: Updated Time stamp) for the delta(only updated ) records. Is any way to know the delta of each table. And how is the delete scenario handled is it hard delete or update with some flag. I also needing the foreign key relationships of System as well as company tables. Is there any documentation pointing to that. Any help would be appreciable.

Thanks,

Melvin

Categories:

Microsoft Dynamics GP

All responses (1)

Answers (0)

Suggested answer

Derek Albaugh on at

Like (0)

Report

RE: How we can find the foreign key relationships in GP system as well as company tables

You should be able to run this script against the system and company databases for Dynamics GP and find the foreign keys in what tables.

I don't know that we have any documentation with FK information. If we did, it would be the SDKs, which we haven't put out a new one since probably GP 2016 or so.

The FK script is this:

SELECT

FK_Table = FK.TABLE_NAME,

FK_Column = CU.COLUMN_NAME,

PK_Table = PK.TABLE_NAME,

PK_Column = PT.COLUMN_NAME,

Constraint_Name = C.CONSTRAINT_NAME

FROM

INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

INNER JOIN

INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

INNER JOIN

INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK

ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

INNER JOIN

(

SELECT

i1.TABLE_NAME, i2.COLUMN_NAME

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2

ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'

) PT

ON PT.TABLE_NAME = PK.TABLE_NAME

-- optional:

ORDER BY

1,2,3,4

Thanks