You’re offline. This is a read only version of the page.
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
Share
Report
All responses (
Answers (
