Oracle Useful Querys

Useful querys ORA

This is the query to get a list of primary keys:

SELECT * 
FROM ALL_CONS_COLUMNS A 
JOIN ALL_CONSTRAINTS C  
ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'P'

And this is the query for foreign keys:

SELECT * 
FROM ALL_CONS_COLUMNS A 
JOIN ALL_CONSTRAINTS C  ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = 'R'

The following query will give all the constraints specified on a table belonging to <owner_name> schema.

SELECT * 
FROM SYS.ALL_CONSTRAINTS A , SYS.ALL_CONS_COLUMNS B
WHERE S.OWNER=<owner_name> AND S.TABLE_NAME=<table_name> AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME