CODEWITHSUNDEEP
sqldatabaseoracle-database
Jules
Jules

Reputation: 245

listing primary keys from data dictionary

I am working on a team that is responsible for data replication to the new database.

For the scripts we need to copy every single primary key in the schema to the scripts. Now this isn't difficult just time consuming to access each table and get the PK.

Is there a way to dynamically list all the PK's from the datadictionary to speed up this process?

Upvotes: 0

Views: 582

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17934

I think this may be close to what you are looking for. There is more information you could include if you wanted from DBA_CONSTRAINTS or DBA_CONS_COLUMNS.

SELECT c.owner,
       c.constraint_name,
       c.table_name,
       c.status,
       LISTAGG (cc.column_name, ',') WITHIN GROUP (ORDER BY cc.position) key_columns
FROM   dba_constraints c
       INNER JOIN dba_cons_columns cc
         ON cc.owner = c.owner
         AND cc.constraint_name = c.constraint_name
         AND cc.table_name = c.table_name
WHERE  c.constraint_type = 'P'
GROUP BY c.owner,
         c.constraint_name,
         c.table_name,
         c.status

Upvotes: 1

Related Questions