Найти, если столбец в Oracle имеет последовательность

Ответ 1

Вы правы; последовательность отдельно от таблицы, и для заполнения любой таблицы может использоваться одна последовательность, а значения в столбце в некоторой таблице могут в основном поступать из последовательности (или набора последовательностей), за исключением значений, созданных вручную.

Другими словами, нет обязательного соединения между столбцом и последовательностью - и, следовательно, нет способа обнаружить такое отношение из схемы.

В конечном счете, анализ будет иметь исходный код всех приложений, которые вставляют или обновляют данные в таблице. Больше ничего не гарантировано. Вы можете уменьшить область поиска, если есть хранимая процедура, которая является единственным способом внести изменения в таблицу, или если есть триггер, который устанавливает значение, или другие подобные вещи. Но общее решение - это "не-решение" анализа "источника".

Ответ 2

Если последовательность используется в триггере, можно найти, какие таблицы она заполняет:

SQL> select t.table_name, d.referenced_name as sequence_name
  2  from   user_triggers t
  3         join user_dependencies d
  4         on d.name = t.trigger_name
  5  where  d.referenced_type = 'SEQUENCE'
  6  and    d.type = 'TRIGGER'
  7  /

TABLE_NAME                     SEQUENCE_NAME
------------------------------ ------------------------------
EMP                            EMPNO_SEQ

SQL>

Вы можете изменить этот запрос, чтобы найти хранимые процедуры и т.д., которые используют последовательность.

Ответ 3

Нет прямых ссылок метаданных между последовательностями Oracle и любым использованием в базе данных. Вы можете сделать разумное предположение, если значения столбца связаны с последовательностью, запросив метаданные USER_SEQUENCES и сравнивая столбцы LAST_NUMBER с данными для столбца.

Ответ 4

Как отметил Джонатан: нет прямого способа связать оба объекта. Однако, если вы "сохраняете стандарт" для первичных ключей и последовательностей/триггеров, вы можете узнать, найдя первичный ключ, а затем связать ограничение с последовательностью таблиц.

Мне было нужно что-то подобное, так как мы создаем продукт multi-db, и я попытался реплицировать некоторые классы с помощью свойств, найденных в объекте DataTable.Net, который имеет AutoIncrement, IncrementSeed и IncrementStep, которые можно найти только в последовательности.

Итак, как я уже сказал, если вы для своих таблиц используете PK и всегда имеете последовательность, связанную с триггером для вставок в таблицу, тогда это может пригодиться:

select tc.table_name,
  case tc.nullable 
    when 'Y' then 1
    else 0
  end as is_nullable,
  case ac.constraint_type 
    when 'P' then 1
    else 0
  end as is_identity,
  ac.constraint_type,
  seq.increment_by as auto_increment_seed,
  seq.min_value as auto_increment_step,
  com.comments as caption,
  tc.column_name,
  tc.data_type,
  tc.data_default as default_value,
  tc.data_length as max_length,
  tc.column_id,
  tc.data_precision as precision,
  tc.data_scale as scale
from SYS.all_tab_columns tc
left outer join SYS.all_col_comments com
  on (tc.column_name = com.column_name and tc.table_name = com.table_name)
LEFT OUTER JOIN  SYS.ALL_CONS_COLUMNS CC 
  on (tc.table_name = cc.table_name and tc.column_name = cc.column_name and tc.owner = cc.owner)
LEFT OUTER JOIN SYS.ALL_CONSTRAINTS AC
  ON (ac.constraint_name = cc.constraint_name and ac.owner = cc.owner)
LEFT outer join user_triggers trg
  on (ac.table_name = trg.table_name and ac.owner = trg.table_owner)
LEFT outer join user_dependencies dep
  on (trg.trigger_name = dep.name and dep.referenced_type='SEQUENCE' and dep.type='TRIGGER')
LEFT outer join user_sequences seq
  on (seq.sequence_name = dep.referenced_name)  
where tc.table_name = 'TABLE_NAME'
and tc.owner = 'SCHEMA_NAME'
AND AC.CONSTRAINT_TYPE = 'P'
union all
select tc.table_name,
  case tc.nullable 
    when 'Y' then 1
    else 0
  end as is_nullable,
  case ac.constraint_type 
    when 'P' then 1
    else 0
  end as is_identity,
  ac.constraint_type,
  seq.increment_by as auto_increment_seed,
  seq.min_value as auto_increment_step,
  com.comments as caption,
  tc.column_name,
  tc.data_type,
  tc.data_default as default_value,
  tc.data_length as max_length,
  tc.column_id,
  tc.data_precision as precision,
  tc.data_scale as scale
from SYS.all_tab_columns tc
left outer join SYS.all_col_comments com
  on (tc.column_name = com.column_name and tc.table_name = com.table_name)
LEFT OUTER JOIN  SYS.ALL_CONS_COLUMNS CC 
  on (tc.table_name = cc.table_name and tc.column_name = cc.column_name and tc.owner = cc.owner)
LEFT OUTER JOIN SYS.ALL_CONSTRAINTS AC
  ON (ac.constraint_name = cc.constraint_name and ac.owner = cc.owner)
LEFT outer join user_triggers trg
  on (ac.table_name = trg.table_name and ac.owner = trg.table_owner)
LEFT outer join user_dependencies dep
  on (trg.trigger_name = dep.name and dep.referenced_type='SEQUENCE' and dep.type='TRIGGER')
LEFT outer join user_sequences seq
  on (seq.sequence_name = dep.referenced_name)  
where tc.table_name = 'TABLE_NAME'
and tc.owner = 'SCHEMA_NAME'
AND AC.CONSTRAINT_TYPE is null;

Это даст вам список столбцов для схемы/таблицы с помощью:

• Имя таблицы
• Если столбец имеет значение NULL
• Тип ограничения (только для ПК)
• Увеличение семян (из последовательности)
• Шаг приращения (из последовательности)
• Комментарии столбца
• Название столбца, конечно:)
• Тип данных
• Значение по умолчанию, если оно есть
• Длина столбца
• Индекс (идентификатор столбца)
• Точность (для чисел)
• Масштаб (для чисел)

Я уверен, что код можно оптимизировать, но он работает для меня, я использую его для загрузки метаданных для таблиц, а затем представляю эти метаданные как сущности на моем интерфейсе.

Обратите внимание, что я фильтрую только первичные ключи и не извлекаю составные ограничения ключей, так как меня это не интересует. Если вы это сделаете, вам придется изменить код, чтобы сделать это, и убедитесь, что вы фильтруете дубликаты, так как вы можете получить один столбец дважды (один для ограничения PK, другой для составного ключа).

Ответ 5

select t.table_name,
   d.referenced_name  as sequence_name,
   d.REFERENCED_OWNER as "OWNER",
   c.COLUMN_NAME
  from user_trigger_cols t, user_dependencies d, user_tab_cols c
 where d.name = t.trigger_name
   and t.TABLE_NAME = c.TABLE_NAME
   and t.COLUMN_NAME = c.COLUMN_NAME
   and d.referenced_type = 'SEQUENCE'
   and d.type = 'TRIGGER'