# 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;
~~~