Last active
December 16, 2015 23:19
-
-
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
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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