How do I transform this t-sql script?
I am not familiar with Oracle and I need to have the equivalent of my script for 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)
a source to share
I'm not familiar with t-sql, but I'm familiar with Oracle, so:
You seem to be querying the data dictionary for all referential integrity constraints for a given table, and for each one to represent the foreign key field (s) and the corresponding field and table for the table they reference.
In Oracle, this information is stored in dictionary views ALL_CONSTRAINTS
and ALL_CONS_COLUMNS
(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:
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 have added constraint names in the query (ForeignConstraint and ReferencedConstraint), which are useful when reference constraints are associated with concatenated keys.
a source to share