1

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'
1

3 Answers 3

1

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';
2
  • Where do you check for uniqueness?
    – miracle173
    Jan 30, 2018 at 13:44
  • @miracle173, should be quite obvious to add this condition. Jan 30, 2018 at 13:58
1

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
3
  • 2
    You should prefer the ANIS join syntax instead of old Oracle join syntax. Jan 30, 2018 at 13:59
  • @WernfriedDomscheit Yeah, in essence you're right. I would probably have used ANSI joins if there were some left/right joins but in this case it's still valid in essentially every dialect. It's just implicit instead of explicit. Old habits die hard. :-P I would also have upvoted your answer as soon as I saw it but I don't have enough reputation. ;-)
    – NOP
    Jan 30, 2018 at 14:49
  • Thank you for your response. This is also giving me the information I need
    – Gautam
    Jan 30, 2018 at 17:18
0

The following PL/SQL procedure deduces a 'number of rows that are null' count for all columns that are indexed for all tables in the schema passed, although you can pass a table name to restrict the procedure to just that table. For any column that contains no nulls, the appropriate 'alter table ... ( column_name not null)' command will be output (make sure you set serveroutput nice and high).

Notes:

The site for whom I wrote this script, had no NOT NULL constraints on any table so the procedure works under this assumption, which may not be suitable for you

This works by scanning every table in the schema, so use your discretion when running this.

(As with any diagnosis script), you should not apply it's recommendations wildly.

create or replace 
procedure check_indexed_columns_for_null(
                 p_schema varchar2, 
                 p_table_name varchar2 default null) is
  cursor x is
     select distinct a.table_name, a.column_name, b.colcount
     from all_ind_columns a, 
     ( select table_name , count(distinct column_name) colcount
       from all_ind_columns
       where index_owner = p_schema
       group by table_name ) b
     where a.index_owner = p_schema
     and b.table_name = a.table_name
     and a.table_name = nvl(upper(p_table_name),a.table_name);

  str0 varchar2(32767); 
  str1 varchar2(32767); 
  str2 varchar2(32767); 
  str3 varchar2(32767);

  prev varchar2(100) := '*';
  cnt number;
  trailer varchar2(5);

procedure do_sql(thesql varchar2) is
  tcursor integer;
  dummy integer;
begin
  tcursor := dbms_sql.open_cursor;
  dbms_sql.parse(tcursor,thesql,2);
  dummy := dbms_sql.execute(tcursor);
  dbms_sql.close_cursor(tcursor);
end;

begin
for i in x loop
  if prev != i.table_name then
    str0 := 'declare ';
    str1 := 'begin select '; str2 := ' into ';
    str3 := ' '; cnt := 1;
  end if;
  if cnt = i.colcount then 
    trailer := ' ';
  else
    trailer := ','||chr(10);
  end if;
  str0 := str0 || 'v'||ltrim(cnt)||' number;';
  str1 := str1 || 'sum(decode('||i.column_name||',null,1,0))'||trailer;
  str2 := str2 || 'v'||ltrim(cnt)||trailer;
  str3 := str3 || 'if v'||ltrim(cnt)||' = 0 then '||
    'dbms_output.put_line(''alter table '||p_schema||'.'||i.table_name||
    ' modify ('||i.column_name||' not null);''); end if;'||chr(10);
  if cnt = i.colcount then
    str2 := str2 ||' from '||p_schema||'.'||i.table_name||';';
    str3 := str3 ||' end;';
    do_sql(str0||' '||str1||' '||str2||' '||str3);
  end if;
  prev := i.table_name;
  cnt := cnt + 1;
end loop;
end;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.