0

Given different data sources I would like to find out all foreign keys inside the database. The DB can be anything from SQLite to SQL Server and Oracle. Is there a generic way to do it? May be an ORM might be helpful?

There are some fine scripts on SO, like this one. But this is SQL Server specific and I want to research the generic nature of the problem.

Community
  • 1
  • 1
Yurii Hohan
  • 4,021
  • 4
  • 40
  • 54

1 Answers1

3

The only hope you have to be RDBMS agnostic is to use the INFORMATION_SCHEMA views, which should be supported by any reasonably ANSI-SQL-compatible database system.

See this SO question on how to find the referential integrity constraints using INFORMAITON_SCHEMA. Queries like that should work on Oracle, MySQL and PostgreSQL etc. - but most likely not in SQLite (not ANSI compatible enough, as far as I can remember)

Community
  • 1
  • 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Oracle does not support the `INFORMATION_SCHEMA` –  Jan 06 '12 at 21:31
  • 2
    @a_horse_with_no_name: **boo**, Oracle! That unfortunately just shows how little value those ANSI SQL standards really have in real life ... :-( – marc_s Jan 06 '12 at 21:32