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
page revision: 2, last edited: 16 Apr 2013 11:06