How to convert this t-sql script?
I am unfamiliar with Oracle and i need to have equivalent of my script for Oracle.
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)
One Solution collect form web for “How to convert this t-sql script?”
I’m unfamiliar with t-sql but I am familiar with Oracle, so:
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_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:
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.