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
Do you by any chance have a version that is for Oracle8 and does not use regexp_replace :(?
ReplyDeleteAppreciate for sharing such good stuff... 👌
ReplyDeleteGet Instant help from our expert for your Database issues or to Optimize your SQL Script. We will be charging on a hourly basis. You can initiate the chat with our expert for the best and fast solution.
ReplyDeleteSite URL: https://sqloptimize.com