title: Retrieving column information (composite key) in SQL
body: <p>I have a large SQL database where I need to verify the structure of the tables and columns (not the data itself). So I need to generate a list of all of the tables, then for each table, all of its columns, then for each column, its data type, length/precision, ordinal position, and whether it's part of the primary key for that table.</p>
  
  <p>I can get most of what I need with the following query:</p>
  
  <pre><code>SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
  FROM INFORMATION_SCHEMA.COLUMNS
  </code></pre>
  
  <p>However, I'm not sure how to check whether a column is part of a primary key. Additionally, for those tables where the PK consists of more than one column, I want to know the ordinal position of each column within the key. The information I've found so far relates to setting the key rather than reading it.</p>
  
  <p>I'm interested in doing this in both SQL Server and Oracle.</p>