Skip to content

Instantly share code, notes, and snippets.

@Andrewpk
Last active December 16, 2015 23:19
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Andrewpk/5513393 to your computer and use it in GitHub Desktop.
Save Andrewpk/5513393 to your computer and use it in GitHub Desktop.
ridiculous query to grab foreign keys for a given table name and/or schema name
SELECT
child.CONSTRAINT_NAME As constraint_name,
coninfo.CONSTRAINT_TYPE as constraint_type,
parent.TABLE_NAME As parent_table_name,
parent.COLUMN_NAME As parent_column_name,
child.TABLE_NAME As child_table_name,
child.COLUMN_NAME As child_column_name,
child.TABLE_SCHEMA As child_table_schema
FROM
QSYS2.SYSKEYCST child
INNER JOIN QSYS2.SYSREFCST crossref ON
child.CONSTRAINT_SCHEMA = crossref.CONSTRAINT_SCHEMA AND
child.CONSTRAINT_NAME = crossref.CONSTRAINT_NAME
INNER JOIN QSYS2.SYSKEYCST parent ON
crossref.UNIQUE_CONSTRAINT_SCHEMA = parent.CONSTRAINT_SCHEMA AND
crossref.UNIQUE_CONSTRAINT_NAME = parent.CONSTRAINT_NAME
INNER JOIN QSYS2.SYSCST coninfo ON
child.CONSTRAINT_NAME = coninfo.CONSTRAINT_NAME
--This is where you might want some WHERE clauses
--Such as:
--WHERE child.TABLE_NAME = :myTableName
--AND child.TABLE_SCHEMA = :myTableSchemaName
ORDER BY child.CONSTRAINT_NAME
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment