I need the following query to only return columns with a UNIQUE constraint:
SELECT col.owner as schema_name,
col.table_name,
column_name,
data_type,
data_precision
FROM sys.dba_tab_cols col
JOIN sys.dba_tables tab on col.owner = tab.owner
and col.table_name = tab.table_name
WHERE data_type in ('NUMBER','BINARY_FLOAT', 'BINARY_DOUBLE','FLOAT')
and data_precision IS NOT NULL
ORDER BY col.owner,
col.table_name,
column_id;
That would require join with yet two tables, the ones that contains info about constraints and columns involved.
I restricted owners to only one; you'll probably want to do the opposite - remove users you aren't interested in, such as SYS, SYSTEM, etc.
SQL> select tab.owner as schema_name,
2 tab.table_Name,
3 col.column_name,
4 col.data_type,
5 col.data_precision,
6 con.constraint_type
7 from dba_tables tab join dba_tab_cols col on tab.owner = col.owner
8 and tab.table_name = col.table_name
9 join dba_constraints con on con.owner = tab.owner
10 and con.table_name = tab.table_name
11 join dba_cons_columns ccol on ccol.owner = con.owner
12 and ccol.table_name = con.table_name
13 and ccol.column_name = col.column_name
14 and ccol.constraint_name = con.constraint_name
15 where tab.owner in ('SCOTT')
16 and col.data_type in ('NUMBER', 'BINARY_FLOAT', 'BINARY_DOUBLE', 'FLOAT')
17 and con.constraint_type = 'U'
18 order by tab.owner, tab.table_name, col.column_name;
SCHEMA_NAME TABLE_NAME COLUMN_NAME DATA_TYPE DATA_PRECISION CONSTRAINT_TYPE
----------- ----------- ----------- --------------- -------------- ---------------
SCOTT TEMP_UNI ID NUMBER U
SCOTT TEST2 ID NUMBER U
SCOTT TEST2 NAME BINARY_FLOAT U
SQL>