转载 SQL Query 比对表结构显示字段名不同的地方

最近有个需要，比对下goldengate 源库 目标库 一些表 表结构 是否一致。当然思路多多，网上搜索下，见到这个链接，觉得写得不错，转载记录下，感谢原作者

https://dataedo.com/kb/query/oracle/compare-tables-and-columns-in-two-schemas

Compare tables and columns in two schemas in Oracle database

Query

select nvl(s1.table_name, s2.table_name) as table_name,
       nvl(s1.column_name, s2.column_name) as column_name,
       s1.column_name as schema_1,
       s2.column_name as schema_2
from ( select table_name,
              column_name
       from sys.all_tab_cols
       where owner = 'schema_1'       -- put schema name to compare here
) s1
full join ( select table_name,
                   column_name
            from sys.all_tab_cols
            where owner = 'schema_2'  -- put schema name to compare here
) s2 on s2.table_name = s1.table_name
     and s2.column_name = s1.column_name
where s1.column_name is null
      or s2.column_name is null
order by table_name,
         column_name;
复制

Columns

• table_name - name of the table with schema
• column_name - name of column
• schema_1 - if column exists in a table in schema 1 then column contains its name (repeats it from column column)
• schema_2 - if column exists in a table in schema 2 then column contains its name (repeats it from column column)

Rows

• One row represents one distinct name of column in specific table.
• Scope of rows: all distinct columns in that exist only in one of the compared databases.
• Ordered by table and column name

Sample results