[SQL] SQL Cookbook - 05. 메타 데이터 쿼리

메타데이터 쿼리는 데이터베이스나 시스템에서 데이터의 특성, 구조, 속성 등을 조회하고 검색하기 위한 쿼리로, 데이터의 정보를 분석하고 관리하는데 사용된다.

 

 

*SMEAGOL이라는 스키마가 있다고 가정함.

1. 스키마의 테이블 목록 보기

특정 스키마에서 생성한 모든 테이블 목록 확인

# information_schema.ta bles를 쿼리
select table_name
 from information_schema.tables
where table_schema = 'SMEAGOL'

 

2. 테이블의 열 나열하기

데이터 유형, 테이블의 열과 해당 열이 있는 테이블의 위치를 나열.

# information_schema.columns를 쿼리 
select column_name, data_type, ordinal_position
 from information_schema.columns
where table_schema = 'SMEAGOL'
 and table_name = 'EMP'

 

3. 테이블의 인덱싱된 열 나열하기

특정 테이블의 인덱스에서 인덱스 색인, 해당 열, 열위치를 나열한다. 

select a.tablename, a.indexname, b.column_name
 from pg_catalog.pg_indexs a,
  information_schema.columns b
where a.schemaname = 'SMEAGOL'
 and a.tablename = b.table_name

 

4. 테이블의 제약조건 나열하기

특정 스키마의 테이블에 대해 정의된 제약조건과, 해당 제약조건이 정의된 열을 나열한다. 

# INFORMATION_SCHEMA.TABLE_CONSTRAINTS
# INFORMATION_SCHEMA.KEY_COLUMN_USAGE
select a.table_name, a.constraint_name, b.column_name, a.constraint_type
 from information_schema.table_constraints a,
      information_schema.key_column_usage b
where a.table_name	= 'EMP'
 and a.table_schema = 'SMEAGOL'
 and a.table_name   = 'b.table_name
 and a.table_schema = b.table_schema
 and a.constraint_name = b.constraint_name

 

5. 관련 인덱스가 없는 외래 키 나열하기

인덱싱되지 않은 외래 키 열을 가진 테이블을 나열한다. (EMP테이블의 외래 키가 인덱싱되는지 확인)

select fkeys.table_name, fkeys.constraint_name, fkeys.column_name, ind_cols.indexname
 from (
  select a.constraint_schema, a.table_name, a.constraint_name, a.column_name
   from information_schema.key_column_usage a,
        information_schema.referential_constraints b
   where a.constraint_name = b.constraint_name
     and a.constraint_schema = b.constraint_schema
     and a.constraint_schema = 'SMEAGOL'
     and a.table_name = 'EMP'
      ) fkeys
      left join
      (
select a.schemaname, a.tablename, a.indexname, b.column_name
 from pg_catalog.pg_indexs a,
      information_schema.columns b
 where a.tablename = b.table_name
  and a.schemaname = b.table_schema
      ) ind_cols
    on ( fkeys.constraint_schema = ind_cols.schemaname
     and fkeys.table_name = ind_cols.tablename
     and fkeys.column_name = ind_cols.column_name )
 where ind_cols.indexname is null

 

6. SQL로 SQL 생성하기

유지관리 작업을 자동화하고자 동적 SQL문을 생성한다.

/* 특정 스키마의 모든 테이블에서 모든 행의 수를 세는 SQL 생성 */
select 'select count(*) from '||table_name||';' cnts
  from user_tables;
  
/* 모든 테이블의 외래 키를 비활성화하기 */
select 'alter table '||table_name||
        ' disable constraint '||constraint_name||';' cons
   from user_constraints
 where constraint_type = 'R';

/* EMP 테이블의 일부 열에 삽입하는 스크립트 생성하기 */
select 'insert into emp(empno, ename, hiredate) '||chr(10)||
       'values( '||empno||','||''''||ename||''',to_date('||''''||hiredate||''') );' inserts
   from emp
 where deptno = 10;

 

7. Oracle에서 데이터 딕셔너리 뷰 확인하기

-- 스키마의 테이블을 설명하는 뷰 확인
select table_name, comments
  from dictionary
 where table_name LIKE '%TABLE%'
 order by table_name;

 

 

 

 

 

 

reference.

[SQL Cookbook] - 한빛미디어