Getting column information (composite key) in SQL

Question

I have a large SQL database where I need to check the structure of tables and columns (and not the data itself). Therefore, I need to create a list of all tables, then for each table, all its columns, then for each column, its data type, length / precision, ordinal position and whether it is part of the primary key for this table.

I can get most of what I need with the following query:

SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE FROM INFORMATION_SCHEMA.COLUMNS 

However, I'm not sure how to check if a column is part of the primary key. In addition, for those tables where PK consists of more than one column, I want to know the ordinal position of each column in the key. The information I have found so far is about installing the key, not reading it.

I am interested in doing this in both SQL Server and Oracle.

Answer 1

In SQL Server you can do

 SELECT K.TABLE_CATALOG, K.TABLE_NAME, K.COLUMN_NAME, K.ORDINAL_POSITION FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON K.TABLE_CATALOG = TC.TABLE_CATALOG AND K.TABLE_SCHEMA = TC.TABLE_SCHEMA AND K.CONSTRAINT_NAME = TC.CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 

or

 SELECT Object_name(C.OBJECT_ID) TABLE_NAME, C.NAME, IC.INDEX_COLUMN_ID FROM SYS.KEY_CONSTRAINTS K INNER JOIN SYS.INDEX_COLUMNS IC ON K.PARENT_OBJECT_ID = IC.OBJECT_ID AND K.UNIQUE_INDEX_ID = IC.INDEX_ID INNER JOIN SYS.COLUMNS C ON IC.OBJECT_ID = C.OBJECT_ID AND IC.COLUMN_ID = C.COLUMN_ID WHERE K.TYPE = 'PK' 

In ORACLE

 SELECT K.OWNER, K.TABLE_NAME, K.INDEX_NAME, C.COLUMN_NAME, C.COLUMN_POSITION FROM ALL_CONSTRAINTS K INNER JOIN ALL_IND_COLUMNS C ON K.OWNER = C.INDEX_OWNER AND K.TABLE_NAME = C.TABLE_NAME AND K.INDEX_NAME = C.INDEX_NAME WHERE K.CONSTRAINT_TYPE = 'P'