Получение информации о столбце (составной ключ) в SQL

#sql #sql-server #oracle

#sql #sql-сервер #Oracle

Вопрос:

У меня есть большая база данных SQL, где мне нужно проверить структуру таблиц и столбцов (а не самих данных). Итак, мне нужно сгенерировать список всех таблиц, затем для каждой таблицы, всех ее столбцов, затем для каждого столбца, его типа данных, длины / точности, порядковой позиции и является ли это частью первичного ключа для этой таблицы.

Я могу получить большую часть того, что мне нужно, с помощью следующего запроса:

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

Однако я не уверен, как проверить, является ли столбец частью первичного ключа. Кроме того, для тех таблиц, где PK состоит из более чем одного столбца, я хочу знать порядковое положение каждого столбца в ключе. Информация, которую я нашел до сих пор, относится к настройке ключа, а не к его чтению.

Я заинтересован в том, чтобы делать это как в SQL Server, так и в Oracle.

1. Вы хотите сказать, что это действительно работает для вас в Oracle? Oracle не поддерживает ни INFORMATION_SCHEMA одно из готовых представлений. Кто-то мог, конечно, создать INFORMATION_SCHEMA схему и построить представления там на основе словаря данных Oracle. Но это будет работать только в базах данных Oracle, в которых установлена эта пользовательская схема.

2. Для Oracle вы хотите запросить представления *_CONSTRAINTS и *_CONS_COLUMNS

3. В настоящее время я использую SQL Server 2008 (и да, он работает); Я имел в виду, что я захочу сделать то же самое в Oracle.

Ответ №1:

В SQL Server вы можете сделать

 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'

или

 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'

В 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'

1. В первом примере K.ORDINAL_POSITION будет возвращена порядковая позиция столбца в таблице, а не порядковая позиция ключа. Насколько я обнаружил, имеет только sys.index_columns эту информацию.

2. @CharlesBurns интересно, я исправлю это завтра. Прошло почти четыре года, день не повредит