title: Create a single query that accesses the system catalog to retrieve the information about all tables and all columns
body: <p>Create a single query that accesses the system catalog to retrieve the information about all tables and all columns, and any constraints on the columns. The information to be displayed is: </p>
  
  <pre><code>a.Table name
  b.Column name (listed in the same order as they were created)
  c.Constraint name
  d.Constraint type. Instead of the one letter codes, use: ‘PK’, ‘FK’, ‘CK’, and ‘NN’
  e.Search condition (for any check constraints), and
  f.Reference table and column for FK constraints 
  </code></pre>
  
  <p>I have created the query that retrieves table name, column names, constraint name, constraint type, search condition.</p>
  
  <p>Here is what I have so far,</p>
  
  <pre><code>SELECT A.table_name, A.column_name,
         B.constraint_name, B.constraint_type,
         search_condition
  FROM(
  (SELECT table_name, column_name, column_id
  FROM user_tab_columns) A
  left JOIN
  (SELECT user_constraints.table_name, column_name,
         user_constraints.constraint_name,
         Decode(constraint_type, 'P', 'PK', 'R', 'FK', 'C', 'CK') constraint_type,
          search_condition
  FROM user_constraints JOIN user_cons_columns
       ON user_cons_columns.constraint_name = user_constraints.constraint_name) B
  ON A.table_name = B.table_name AND A.column_name = B.column_name)
  ORDER BY A.table_name, column_id;
  </code></pre>
  
  <p>I am having trouble finding the referenced columns to which the FK refers and the referenced tables.</p>
  
  <p>Thanks.</p>