컬럼 비교용 SQL 작성(ANSI-SQL) - except, intersect

1. 특정 테이블에만 존재하는 컬럼정보 확인

1-1. 확인하고자 하는 테이블 : table_name_target

1-2. 비교 소스 테이블 : table_name_source

select b.* from 
(
select column_name from INFORMATION_SCHEMA.columns
where table_catalog = 'schema_name'
and table_schema = 'schema_name'
and table_name = 'table_name_target'
except
select column_name from INFORMATION_SCHEMA.columns
where table_catalog = 'schema_name'
and table_schema = 'schema_name'
and table_name = 'table_name_source') a,
(SELECT
PS.RELNAME AS TABLE_NAME,
PA.ATTNAME AS COLUMN_NAME,
PD.DESCRIPTION AS COLUMN_COMMENT
FROM PG_STAT_ALL_TABLES PS, PG_DESCRIPTION PD, PG_ATTRIBUTE PA
WHERE PD.OBJSUBID<>0
AND PS.RELID=PD.OBJOID
AND PD.OBJOID=PA.ATTRELID
AND PD.OBJSUBID=PA.ATTNUM
AND PS.SCHEMANAME='schema_name'
AND PS.RELNAME='table_name_target') b
where a.COLUMN_NAME = b.COLUMN_NAME

 

 

 

2. 두 테이블간 공통 컬럼 정보 확인

2-1. 확인하고자 하는 테이블 : table_name_target

2-2. 비교 소스 테이블 : table_name_source

 

select b.* from 
(
select column_name from INFORMATION_SCHEMA.columns
where table_catalog = 'schema_name'
and table_schema = 'schema_name'
and table_name = 'table_name_target'
intersect 
select column_name from INFORMATION_SCHEMA.columns
where table_catalog = 'schema_name'
and table_schema = 'schema_name'
and table_name = 'table_name_source') a,
(SELECT
PS.RELNAME AS TABLE_NAME,
PA.ATTNAME AS COLUMN_NAME,
PD.DESCRIPTION AS COLUMN_COMMENT
FROM PG_STAT_ALL_TABLES PS, PG_DESCRIPTION PD, PG_ATTRIBUTE PA
WHERE PD.OBJSUBID<>0
AND PS.RELID=PD.OBJOID
AND PD.OBJOID=PA.ATTRELID
AND PD.OBJSUBID=PA.ATTNUM
AND PS.SCHEMANAME='schema_name'
AND PS.RELNAME='table_name_target') b
where a.COLUMN_NAME = b.COLUMN_NAME