Oracle sqls

oracle.md

Oracle sqls

find all constraints of a table

select * from all_constraints where table_name='TABLE_NAME' and owner='OWNER_NAME';

find all primary keys of a table

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE_NAME' and cons.owner= 'OWNER_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

find all table comments

select * from all_tab_comments where owner = 'OWNER_NAME' and table_name ='TABLE_NAME';

find all column comments

select * from all_col_comments where owner = 'OWNER_NAME' and table_name ='TABLE_NAME';

find source of stored proc

select text from all_source where owner='OWNER_NAME' and name='PROC_NAME' order by line;

Extract table definition (DDL statements)

select DBMS_METADATA.GET_DDL('TABLE','<TABLE_NAME>','<SCHEMA_NAME>') from DUAL;