select * from all_tab_columns where table_name='tablename1' select * from all_tab_columns where table_name='tablename2' 再比较字段就可以了
我是要用sql语句来比较,不是用工具 还有我是要自动比较,然后返回不同的值
select * from table_name1 minus select * from table_name2
jiffer(爱工作,更爱老婆) 的做法是错的。
select case when exists ( select column_name, data_type, data_length from all_tab_columns a where table_name = upper(&Tab_A) and not exists (select column_name, data_type, data_length from all_tab_columns b where table_name = upper(&Tab_B) and a.column_name = b.column_name and a.data_type = b.data_type and a.data_length = b.data_type) ) then '存在A表中字段不存在B表中' when exists ( select column_name, data_type, data_length from all_tab_columns a where table_name = upper(&Tab_B) and not exists (select column_name, data_type, data_length from all_tab_columns b where table_name = upper(&Tab_A) and a.column_name = b.column_name and a.data_type = b.data_type and a.data_length = b.data_type) ) then '存在B表中字段不存在A表中' else '表结构一致' end from dual
from all_tab_columns where table_name='tablename1'
select *
from all_tab_columns where table_name='tablename2'
再比较字段就可以了
还有我是要自动比较,然后返回不同的值
minus
select * from table_name2
select column_name, data_type, data_length from all_tab_columns a
where table_name = upper(&Tab_A)
and not exists
(select column_name, data_type, data_length from all_tab_columns b
where table_name = upper(&Tab_B)
and a.column_name = b.column_name and a.data_type = b.data_type and a.data_length = b.data_type)
) then '存在A表中字段不存在B表中'
when exists (
select column_name, data_type, data_length from all_tab_columns a
where table_name = upper(&Tab_B)
and not exists
(select column_name, data_type, data_length from all_tab_columns b
where table_name = upper(&Tab_A)
and a.column_name = b.column_name and a.data_type = b.data_type and a.data_length = b.data_type)
) then '存在B表中字段不存在A表中'
else '表结构一致'
end
from dual