INFORMATION_SCHEMA

1. INFORMATION_SCHEMA

란 무엇인가 ??

 SQL 서버 내에 존재하는 DB들의 메타데이터를 담고 있는, 읽기 전용(Read-only) 가상 DB

라고 할 수 있다.

 

MySQL 외에 PostgreSQL, SQL Server 등 대부분의 DBMS는 INFORMATION_SCHEMA를 사용할 수 있게 되어 있고, 실제로 데이터를 담고 있는 것이 아니라 서버에서 데이터를 조회해서 가져오는 것이다.

이를 통해 사용자는 여러 DB들의 메타데이터를 조회할 수 있다. 읽기 전용이기에 조회만 되고 수정은 안 된다.

 

** 여기서 잠깐, 메타데이터란건 뭘까?

      => 메타데이터는 다른 데이터를 설명하거나 분류하거나 이해하는 데 도움을 주는 정보로, 쉽게 말해 "데이터의 데이터" 라 할 수 있다.                 테이블, 컬럼, 인덱스, 테이블 제약 조건, 접근 권한 등이 있다.

 

 

INFORMATION_SCHEMA에서 조회 가능한 뷰는 다음과 같은 것들이 있고, 각 뷰에 있는 컬럼들도 굉장히 많다.

 

 - TABLES: 데이터베이스의 모든 테이블에 대한 정보 - COLUMNS: 모든 테이블의 칼럼에 대한 세부 정보
 - TABLE_CONSTRAINTS: 테이블의 제약 조건(예: PRIMARY KEY, FOREIGN KEY)에 대한 정보
 - KEY_COLUMN_USAGE: 키와 인덱스에 대한 칼럼 사용 정보
 - SCHEMATA: 데이터베이스의 스키마에 대한 정보
 - VIEWS: 뷰에 대한 세부 정보
 - ROUTINES: 저장 프로시저와 함수에 대한 정보
 - TRIGGERS: 트리거에 대한 정보
 - REFERENTIAL_CONSTRAINTS: 외래 키 제약 조건에 대한 정보

 

예를 들어 테이블에 대한 정보(이름이나 행 갯수)를 보고 싶으면 TABLES라는 뷰를 조회하면 되는 것.

물론 이것들 말고도 더 많다. show tables from information_schema; 쿼리문을 실행하면 information_schema 뷰가 갖고 있는 테이블을 모두 조회할 수 있다.

 

그 외의  자세한 내용은 공식 웹사이트를 참고해보도록 하자.

https://dev.mysql.com/doc/refman/8.0/en/information-schema.html

MySQL :: MySQL 8.0 Reference Manual :: 26 INFORMATION_SCHEMA Tables

 

 

2. 어떻게 사용하는가?

MySQL 환경에서 실제 데이터로 몇 개의 예시를 들어 보겠다.

* 사용 데이터 : Kaggle의 브라질 Olist 데이터

 

1. 특정 DB에 있는 테이블들의 이름과, 각 테이블이 갖고 있는 행의 수가 궁금하다면?

 

 1) FROM information_schema.TABLES

   => information_schema 뷰의 TABLES 테이블로부터

 2) WHERE TABLE_SCHEMA = 'brazilian_olist'

   => brazilian_olist라는 이름의 스키마(DB)의

 3) SELECT TABLE_NAME, TABLE_ROWS

   => 모든 테이블의 이름과 각 테이블의 행 수를 출력하겠다.

 

 

 

2. 특정 DB에 있는 테이블들 중 특정 문자가 포함된 컬럼명을 가진 테이블과, 해당 컬럼명이 궁금하다면?

 

 1) FROM information_schema.COLUMNS

   => information_schema 뷰의 COLUMNS 테이블로부터

 2) WHERE TABLE_SCHEMA = 'brazilian_olist' AND COLUMN_NAME LIKE '%orders%'

   => DB 이름이 brazilian_olist이고 orders가 포함된 이름의 컬럼을 갖는

 3) SELECT TABLE_NAME, COLUMN_NAME

   => 테이블의 이름과 해당 컬럼 출력.

 

 

 

여러번 사용해서 익숙해지기만 한다면, 이래저래 꽤 유용하게 사용할 수 있을 것 같다!