/doc/notes/postgres_meta_info.sql

http://zoop.googlecode.com/ · SQL · 29 lines · 28 code · 1 blank · 0 comment · 0 complexity · a0a0d076da748e65f1e81f0f0758c2ca MD5 · raw file

  1. http://www.alberton.info/postgresql_meta_info.html
  2. SELECT * from information_schema.tables where table_schema = 'public';
  3. SELECT * from information_schema.columns where table_schema = 'public';
  4. SELECT tc.constraint_name,
  5. tc.constraint_type,
  6. tc.table_name,
  7. kcu.column_name,
  8. tc.is_deferrable,
  9. tc.initially_deferred,
  10. rc.match_option AS match_type,
  11. rc.update_rule AS on_update,
  12. rc.delete_rule AS on_delete,
  13. ccu.table_name AS references_table,
  14. ccu.column_name AS references_field
  15. FROM information_schema.table_constraints tc
  16. LEFT JOIN information_schema.key_column_usage kcu
  17. ON tc.constraint_catalog = kcu.constraint_catalog
  18. AND tc.constraint_schema = kcu.constraint_schema
  19. AND tc.constraint_name = kcu.constraint_name
  20. LEFT JOIN information_schema.referential_constraints rc
  21. ON tc.constraint_catalog = rc.constraint_catalog
  22. AND tc.constraint_schema = rc.constraint_schema
  23. AND tc.constraint_name = rc.constraint_name
  24. LEFT JOIN information_schema.constraint_column_usage ccu
  25. ON rc.unique_constraint_catalog = ccu.constraint_catalog
  26. AND rc.unique_constraint_schema = ccu.constraint_schema
  27. AND rc.unique_constraint_name = ccu.constraint_name
  28. where constraint_type in ('FOREIGN KEY', 'PRIMARY KEY');