title: How to find primary keys for all foreign keys in a DB using INFORMATION_SCHEMA
body: <p>I want to find all foreign keys in a table, and for each foreign key find the primary key table &amp; column it points to. I need to do this using INFORMATION_SCHEMA because we want to use this against all DB vendors (or at least all that properly implement INFORMATION_SCHEMA).</p>
  
  <p>The best I have come up with is:</p>
  
  <pre><code>"SELECT k.COLUMN_NAME, ccu.TABLE_NAME AS 'references_table', ccu.COLUMN_NAME AS 'references_field' " +
  "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k LEFT OUTER JOIN " +
  "INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c ON k.TABLE_NAME = c.TABLE_NAME AND k.TABLE_SCHEMA = c.TABLE_SCHEMA AND " +
  "k.TABLE_CATALOG = c.TABLE_CATALOG AND k.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND " +
  "k.CONSTRAINT_NAME = c.CONSTRAINT_NAME LEFT OUTER JOIN " +
  "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc ON rc.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA AND " +
  "rc.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG AND rc.CONSTRAINT_NAME = c.CONSTRAINT_NAME LEFT OUTER JOIN " +
  "INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON rc.UNIQUE_CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA AND " +
  "rc.UNIQUE_CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG AND rc.UNIQUE_CONSTRAINT_NAME = ccu.CONSTRAINT_NAME " +
  "WHERE(k.CONSTRAINT_CATALOG = DB_NAME()) AND (k.TABLE_NAME = '" + table.Name + "') AND (c.CONSTRAINT_TYPE = 'FOREIGN KEY')";
  </code></pre>
  
  <p>This strikes me as over-complicated. Is there a better select for this?</p>
  
  <p>thanks - dave</p>