5
Hi
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
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
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
290,558
Super User 2024 Season 2
#2
Martin Dráb
228,645
Most Valuable Professional
#3
nmaenpaa
101,148
Share
Report
All responses (
Answers (