Skip to content

Instantly share code, notes, and snippets.

@taher435
Created April 7, 2020 16:47
Show Gist options
  • Save taher435/f3d34f53cf72859fd9b0293efd03cf1b to your computer and use it in GitHub Desktop.
Save taher435/f3d34f53cf72859fd9b0293efd03cf1b to your computer and use it in GitHub Desktop.
Fetch all dependent foreign key tables for given table and column name.
SELECT rc.constraint_catalog,
rc.constraint_schema||'.'||tc.table_name AS table_name,
kcu.column_name,
match_option,
update_rule,
delete_rule
FROM information_schema.referential_constraints AS rc
JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
JOIN information_schema.key_column_usage AS ccu ON(ccu.constraint_catalog=rc.unique_constraint_catalog AND ccu.constraint_schema=rc.unique_constraint_schema AND ccu.constraint_name=rc.unique_constraint_name)
WHERE ccu.table_catalog='catalog'
AND ccu.table_schema='public'
AND ccu.table_name='table'
AND ccu.column_name='column';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment