Compare Structures of Two Tables in Oracle

There are cases where we want to compare the structures of two tables and alter the first table structure as per the second table.

I have created the following script to compare two tables structure and to generate the ALTER commands which will sync structure of 'dest_table' table as per the structure of 'src_table'.

set serveroutput on

declare
  l_str_size varchar2(100);
  l_str_query varchar2(2000);
  l_default1 varchar(4000);
  l_default2 varchar(4000);    
  p_dest_table varchar2(30); 
  p_src_table varchar2(30);
begin
  p_dest_table := UPPER('&dest_table');
  p_src_table := UPPER('&src_table');
  for c in
  (
    select * from
      (select table_name t1, column_name c1, data_type dt1 , nvl(data_precision,data_length) dp1, 
      data_scale ds1, NULLABLE n1, DATA_DEFAULT d1 from user_tab_columns 
      where table_name = p_dest_table) dest
      FULL OUTER JOIN
      (select table_name t2, column_name c2, data_type dt2, nvl(data_precision,data_length) dp2, 
      data_scale ds2, NULLABLE n2, DATA_DEFAULT d2 from user_tab_columns 
      where table_name = p_src_table) src
      on c1 = c2
  )
  loop
    l_default1 := regexp_replace(c.d1,'[[:space:]]');
    l_default2 := regexp_replace(c.d2,'[[:space:]]');
    
    -- column altered
    if c.c1 is not null and c.c2 is not null then
      l_str_query := 'xyz';
      if c.dt1 <> c.dt2 or nvl(c.dp1,'-999') <> nvl (c.dp2,'-999') 
      or  nvl(c.ds1,'-999') <> nvl (c.ds2,'-999') or c.n1 <> c.n2 
      or nvl(l_default1,'xyz') <> nvl(l_default2,'xyz') then
        l_str_size := '(';
        if c.dp2 is not null then
          l_str_size :=l_str_size || c.dp2;
        end if;
        if c.ds2 is not null then
          l_str_size :=l_str_size || ',' || c.ds2;
        end if;
        l_str_size := l_str_size || ')';    
        l_str_query := 'alter table ' || p_dest_table || ' modify ' || c.c2 || ' ' || c.dt2;
        if l_str_size <> '()' and c.dt2 <> 'DATE' then
          l_str_query := l_str_query || l_str_size;
        end if;
        if l_default2 is not null then
          l_str_query := l_str_query || ' default ' ||  l_default2;
        end if;                
        if c.n2 = 'N' and  c.n1 = 'Y' then
          l_str_query := l_str_query || ' not null enable novalidate';
        end if;
        if c.n2 = 'Y' and  c.n1 = 'N' then
          l_str_query := l_str_query || ' null';
        end if;
      end if;
      if l_str_query <> 'xyz' then
        dbms_output.put_line(l_str_query || ';');
      end if;
    end if;
    
    -- column added
    if c.c1 is null and c.c2 is not null then
      l_str_size := '(';
      if c.dp2 is not null then
        l_str_size :=l_str_size || c.dp2;
      end if;
      if c.ds2 is not null then
        l_str_size :=l_str_size || ',' || c.ds2;
      end if;
      l_str_size := l_str_size || ')';            
      l_str_query := 'alter table ' || p_dest_table || ' add ' || c.c2 || ' ' || c.dt2;
      if l_str_size <> '()' and c.dt2 <> 'DATE' then
        l_str_query := l_str_query || l_str_size;
      end if;
      if l_default2 is not null then
        l_str_query := l_str_query || ' default ' ||  l_default2;
      end if;            
      if c.n2 = 'N' then
        l_str_query := l_str_query || ' not null';
      end if;
      dbms_output.put_line(l_str_query || ';');
    end if;        
    
    -- column deleted
    if c.c1 is not null and c.c2 is null then
      l_str_query := 'alter table ' || p_dest_table || ' drop column ' || c.c1;
      dbms_output.put_line(l_str_query || ';');
    end if;            
  end loop;
end;
/

This script will take care of following Table Structure Mismatch
- Columns to be Added
- Columns to be Dropped
- Columns to be Resized
- Columns to be marked as NULL or Not NULL
- Columns to be modified for default Value