查找 Oracle 中的列是否具有序列

Find if a column in Oracle has a sequence

提问by stimms

I am attempting to figure out if a column in Oracle is populated from a sequence. My impression of how Oracle handles sequencing is that the sequence and column are separate entities and one needs to either manually insert the next sequence value like:

我试图弄清楚 Oracle 中的列是否是从序列填充的。我对 Oracle 如何处理排序的印象是序列和列是单独的实体，需要手动插入下一个序列值，例如：

insert into tbl1 values(someseq.nextval, 'test')

or put it into a table trigger. Meaning that it is non-trivial to tell if a column is populated from a sequence. Is that correct? Any ideas about how I might go about figuring out if a column is populated from a sequence?

或将其放入表触发器中。这意味着判断一列是否是从序列中填充的并非易事。那是对的吗？关于如何确定列是否从序列填充的任何想法？

采纳答案by Jonathan Leffler

You are correct; the sequence is separate from the table, and a single sequence can be used to populate any table, and the values in a column in some table may mostly come from a sequence (or set of sequences), except for the values manually generated.

你是对的; 序列与表分离，单个序列可用于填充任何表，某些表中某一列中的值可能主要来自一个序列（或一组序列），但手动生成的值除外。

In other words, there is no mandatory connection between a column and a sequence - and therefore no way to discover such a relationship from the schema.

换句话说，列和序列之间没有强制连接——因此无法从模式中发现这种关系。

Ultimately, the analysis will be of the source code of all applications that insert or update data in the table. Nothing else is guaranteed. You can reduce the scope of the search if there is a stored procedure that is the only way to make modifications to the table, or if there is a trigger that sets the value, or other such things. But the general solution is the 'non-solution' of 'analyze the source'.

最终，将分析所有在表中插入或更新数据的应用程序的源代码。其他什么都不能保证。如果有一个存储过程是对表进行修改的唯一方法，或者如果有设置值的触发器或其他类似的东西，您可以缩小搜索范围。但一般的解决方案是“分析来源”的“非解决方案”。

回答by APC

If the sequence is used in a trigger, it is possible to find which tables it populates:

如果在触发器中使用该序列，则可以找到它填充的表：

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>

You can vary this query to find stored procedures, etc that make use of the sequence.

您可以更改此查询以查找使用该序列的存储过程等。

回答by dpbradley

There are no direct metadata links between Oracle sequences and any use in the database. You could make an intelligent guess if a column's values are related to a sequence by querying the USER_SEQUENCES metadata and comparing the LAST_NUMBER column to the data for the column.

Oracle 序列与数据库中的任何使用之间没有直接的元数据链接。您可以通过查询 USER_SEQUENCES 元数据并将 LAST_NUMBER 列与该列的数据进行比较来智能猜测列的值是否与序列相关。

回答by Gustavo Rubio

As Jonathan pointed out: there is no direct way to relate both objects. However, if you "keep a standard" for primary keys and sequences/triggers you could find out by finding the primary key and then associate the constraint to the table sequence.

正如乔纳森指出的那样：没有直接的方法来关联这两个对象。但是，如果您对主键和序列/触发器“保持标准”，您可以通过找到主键然后将约束关联到表序列来找出答案。

I was in need of something similar since we are building a multi-db product and I tried to replicate some classes with properties found in a DataTable object from .Net which has AutoIncrement, IncrementSeed and IncrementStep which can only be found in the sequences.

我需要类似的东西，因为我们正在构建一个多数据库产品，我试图复制一些具有在 .Net 的 DataTable 对象中找到的属性的类，它具有 AutoIncrement、IncrementSeed 和 IncrementStep，它们只能在序列中找到。

So, as I said, if you, for your tables, use a PK and always have a sequence associated with a trigger for inserts on a table then this may come handy:

因此，正如我所说，如果您对您的表使用 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;

That would give you the list of columns for a schema/table with:

这将为您提供架构/表的列列表：

• Table name
• If column is nullable
• Constraint type (only for PK's)
• Increment seed (from the sequence)
• Increment step (from the sequence)
• Column comments
• Column name, of course :)
• Data type
• Default value, if any
• Length of column
• Index (column id)
• Precision (for numbers)
• Scale (for numbers)

• 表名
• 如果列可以为空
• 约束类型（仅适用于 PK）
• 增加种子（来自序列）
• 递增步长（从序列中）
• 专栏评论
• 列名，当然:)
• 数据类型
• 默认值，如果有
• 柱长
• 索引（列 id）
• 精度（对于数字）
• 比例（数字）

I'm pretty sure that code can be optimized but it works for me, I use it to "load metadata" for tables and then represent that metadata as entities on my frontend.

我很确定可以优化代码，但它对我有用，我用它来“加载表的元数据”，然后将该元数据表示为前端上的实体。

Note that I'm filtering only primary keys and not retrieving compound key constraints since I don't care about those. If you do you'll have to modify the code to do so and make sure that you filter duplicates since you could get one column twice (one for the PK constraint, another for the compound key).

请注意，我只过滤主键而不检索复合键约束，因为我不关心这些。如果这样做，您将必须修改代码才能这样做，并确保过滤重复项，因为您可以两次获得一列（一列用于 PK 约束，另一列用于复合键）。

回答by Ali

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'