Skip to content

Instantly share code, notes, and snippets.

@QuittyMR

QuittyMR/sys_get_references

Created July 17, 2020 09:34
Show Gist options
  • Save QuittyMR/9f9290e5eb0d210946256ca6a226a3c0 to your computer and use it in GitHub Desktop.
Save QuittyMR/9f9290e5eb0d210946256ca6a226a3c0 to your computer and use it in GitHub Desktop.
Download ZIP
Collect all FK references in a Postgres DB
Raw
sys_get_references
SELECT tc.constraint_name,
concat(tc.table_schema, '.', tc.table_name) AS referencing_table,
kcu.column_name AS referencing_column,
concat(ccu.table_schema, '.', ccu.table_name) AS src_table,
ccu.column_name AS src_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name::text = kcu.constraint_name::text
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name::text = tc.constraint_name::text
WHERE tc.constraint_type::text = 'FOREIGN KEY'::text;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment