Original: Statement for obtaining the field name constraint name of the primary and foreign key table name in Oracle and sqlserver

I hope that I can clearly analyze the primary and foreign key tables and field information like SQL Server, use an SQL statement to write out the one-to-one correspondence between the fields in the primary and foreign key tables, and find them online for half a day, the primary and foreign key tables and fields can be listed, but the fields are not exactly one-to-one, so I wrote one myself.

 

Obtain the name field name constraint of the primary and foreign key table in Oracle
Select B. table_name as pktable_name, B. column_name pkcolumn_name, C. table_name fktable_name, C. column_name fkcolumn_name, C. position ke_seq, C. constraint_name fk_name from (select * From user_cons_columns) B left join (select * From user_constraints where user_constraints.constraint_type = 'R') A on B. constraint_name =. r_constraint_name left join user_cons_columns C on C. constraint_name =. constraint_name where c. position is not null and C. position = B. position order by C. table_name, C. position

 

Queries the ing between a table's foreign key table and foreign key fields.

Query the ing between the foreign key table of the pktable_name table and the constraint name.
String SQL = "select

B. table_name, B. column_name, B. Position, C. table_name, C. column_name, C. Position

, C. constraint_name from (select * From user_cons_columns where

Table_name = '"+ pktable_name +"') B left join (select * From user_constraints

Where user_constraints.constraint_type = 'r' and r_constraint_name like

'% "+ Pktable_name +"') A on B. constraint_name = A. r_constraint_name left join

User_cons_columns C on C. constraint_name = A. constraint_name where

C. Position is not null and C. Position = B. Position order

C. table_name, C. Position"

 

 

 

SQL Server executes the Stored Procedure

1

Check the ing between the primary and foreign key tables and fields in sqlserver
Sp_fkeys pktable_name

Pktable_name refers to the name of the primary key table.

2

 

Code
Obtain the table's primary and foreign key constraints
Exec sp_helpconstraint 'fktable _ name ';

Fktable_name is foreign key table's name