如何转换这个t-sql脚本？

I am unfamiliar with Oracle and i need to have equivalent of my script for Oracle.

我不熟悉Oracle,我需要有相同的Oracle脚本。

Script:


SELECT  COL_NAME(parent_object_id, parent_column_id) AS ForigneKeyField,
        COL_NAME(referenced_object_id, referenced_column_id) AS PrimaryKeyField,
        OBJECT_NAME(referenced_object_id) AS PrimaryTable
FROM    sys.foreign_key_columns
WHERE   parent_object_id = OBJECT_ID(@name)

1 个解决方案

#1

I'm unfamiliar with t-sql but I am familiar with Oracle, so:

我不熟悉t-sql,但我熟悉Oracle,所以:

It appears you are querying the data dictionary for all referential integrity constraints for a given table, and for each, reporting the foreign key field(s), and the relevant field and table for the table they refer to.

您似乎在查询数据字典中是否存在给定表的所有参照完整性约束,并且每个参数都报告外键字段,以及它们引用的表的相关字段和表。

In Oracle this information is kept in the ALL_CONSTRAINTS and ALL_CONS_COLUMNS data dictionary views (or DBA_CONSTRAINTS / DBA_CONS_COLUMNS or USER_CONSTRAINTS / USER_CONS_COLUMNS, depending on what the scope of your query should be). I would run a query like this:

在Oracle中,此信息保存在ALL_CONSTRAINTS和ALL_CONS_COLUMNS数据字典视图中(或DBA_CONSTRAINTS / DBA_CONS_COLUMNS或USER_CONSTRAINTS / USER_CONS_COLUMNS,具体取决于查询的范围)。我会运行这样的查询:

SELECT fc.constraint_name   AS ForeignConstraint
      ,fc.r_constraint_name AS ReferencedConstraint
      ,fcc.column_name      AS ForeignKeyField
      ,rcc.column_name      AS ReferencedKeyField
      ,rc.table_name        AS ReferencedTable
FROM   sys.all_constraints  fc
      ,sys.all_constraints  rc
      ,sys.all_cons_columns fcc
      ,sys.all_cons_columns rcc
WHERE  fc.table_name        = :name
AND    fc.constraint_type   = 'R'
AND    fc.r_constraint_name = rc.constraint_name
AND    fc.constraint_name   = fcc.constraint_name
AND    rc.constraint_name   = rcc.constraint_name
AND    fcc.position         = rcc.position
ORDER BY fc.constraint_name, fcc.position;

I've added the constraint names in the query (ForeignConstraint and ReferencedConstraint) which are useful when referential constraints involve concatenated keys.

我在查询(ForeignConstraint和ReferencedConstraint)中添加了约束名称,这在参照约束涉及连接键时很有用。

#1