Question: I am trying to write a query that displays all unique columns that are allowed to be null in Oracle. 

The information I need is in these 3 tables:

* ALL\_TAB\_COLUMNS
* ALL\_IND\_COLUMNS
* ALL\_INDEXES

I am having difficulty joining these tables to display the information I'm looking for. Any help would be appreciated.

Looking for output such as:

```
OWNER, COLUMN_NAME, INDEX, UNIQUENESS, NULLABLE

WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE'

```

Answer: 
Try this one:

```
SELECT ind.OWNER AS INDEX_OWNER, ind.INDEX_NAME,
    tab.OWNER AS TABLE_OWNER, tab.table_name,
    tab.column_Name,
    UNIQUENESS, NULLABLE
FROM ALL_INDEXES ind
    JOIN ALL_IND_COLUMNS col 
        ON ind.OWNER = INDEX_OWNER 
            AND ind.INDEX_NAME = col.INDEX_NAME
    JOIN ALL_TAB_COLUMNS tab 
        ON tab.owner = col.table_owner 
            AND tab.table_name = col.table_name 
            AND tab.column_Name = col.column_Name
WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE';

```
If the only output you're looking for is the one that you described, you could get away with using a different set of tables. As you're displaying the column name I added the position for the column in case of an multi column index.

```
select
  i.owner            as ind_owner,
  i.table_owner      as tab_owner,
  i.table_name       as tab_name,
  i.index_name       as ind_name,
  ic.column_position as col_pos,
  ic.column_name     as col_name
from
  all_indexes     i,
  all_ind_columns ic,
  all_tab_columns c
where i.table_owner  = ic.table_owner
  and i.table_owner  = c.owner
  and ic.column_name = c.column_name
  and ic.table_name  = c.table_name
  and i.table_name   = ic.table_name
  and i.index_name   = ic.index_name
  and i.UNIQUENESS   = 'UNIQUE'
  and c.nullable     = 'Y'
order by
  i.owner,
  ic.table_name,
  i.index_name,
  ic.column_position

```