(SQL Server) INFORMATION_SCHEMA: 데이터베이스 정보 탐색의 지름길

INFORMATION_SCHEMA 뷰를 활용한 유용한 쿼리 가이드

SQL Server에서 INFORMATION_SCHEMA는 데이터베이스의 메타데이터(데이터에 대한 데이터)를 표준화된 방법으로 제공하는 뷰 집합입니다. 이는 데이터베이스 객체(테이블, 뷰, 프로시저 등)에 대한 정보를 간편하게 조회할 수 있게 해주죠. 시스템 카탈로그 뷰와 유사하지만, ANSI-SQL 표준을 따르기 때문에 다른 SQL 데이터베이스 시스템에서도 유사한 쿼리를 사용할 수 있다는 장점이 있습니다.

---

1. INFORMATION_SCHEMA.TABLES

데이터베이스 내의 모든 테이블과 뷰에 대한 정보를 제공합니다. 특정 스키마에 속한 테이블 목록을 확인하거나, 뷰와 테이블을 구분해서 보고 싶을 때 유용합니다.

• 사용 용도:
  • 현재 데이터베이스에 존재하는 모든 테이블 목록 조회
  • 특정 스키마(예: dbo)에 속한 테이블만 필터링
  • 테이블(BASE TABLE)과 뷰(VIEW)를 구분하여 확인
• 예시 쿼리

  -- 현재 데이터베이스의 모든 테이블 및 뷰 정보 조회
  SELECT *
  FROM INFORMATION_SCHEMA.TABLES;
  
  -- 'dbo' 스키마에 속한 테이블만 조회
  SELECT TABLE_NAME, TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_SCHEMA = 'dbo'
  AND TABLE_TYPE = 'BASE TABLE';
  

반응형

---

2. INFORMATION_SCHEMA.COLUMNS

데이터베이스 내의 모든 테이블과 뷰에 있는 열(컬럼)에 대한 정보를 제공합니다. 특정 테이블의 컬럼 목록, 데이터 타입, 최대 길이 등을 확인할 때 사용됩니다.

• 사용 용도:
  • 특정 테이블의 모든 컬럼 목록 및 속성 확인
  • 컬럼의 데이터 타입, 최대 문자 길이 등 상세 정보 조회
  • 데이터베이스 내에서 특정 이름의 컬럼이 사용되는 모든 테이블 찾기
• 예시 쿼리

  -- 'Products' 테이블의 모든 컬럼 정보 조회
  SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'Products';
  

---

3. INFORMATION_SCHEMA.ROUTINES

저장 프로시저, 함수 등과 같은 루틴에 대한 정보를 제공합니다. 특정 프로시저의 정의나 생성일 등을 확인할 때 유용합니다.

• 사용 용도:
  • 데이터베이스에 있는 모든 저장 프로시저 및 함수 목록 확인
  • 특정 루틴의 타입(예: 'PROCEDURE' 또는 'FUNCTION') 조회
• 예시 쿼리

• -- 모든 저장 프로시저 목록 및 생성 정보 조회
  SELECT ROUTINE_NAME, ROUTINE_TYPE, CREATED
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_TYPE = 'PROCEDURE';
  

---

4. INFORMATION_SCHEMA.KEY_COLUMN_USAGE

기본 키(Primary Key)와 외래 키(Foreign Key)와 같은 제약 조건에 대한 정보를 제공합니다. 특정 테이블의 키 관계를 파악하는 데 매우 유용합니다.

• 사용 용도:
  • 어떤 테이블의 어떤 컬럼이 키로 사용되는지 확인
  • 테이블 간의 외래 키 관계 파악
• 예시 쿼리

• -- 'Orders' 테이블에 걸려있는 키(Primary, Foreign) 정보 조회
  SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_NAME = 'Orders';
  

---

INFORMATION_SCHEMA 뷰는 데이터베이스 관리자(DBA)나 개발자가 데이터베이스의 구조를 빠르게 파악하고 분석하는 데 있어 필수적인 도구입니다. 복잡한 시스템 뷰보다 직관적이고 표준화된 쿼리 방식을 제공하기 때문에, 데이터베이스의 스키마를 이해하고 관리하는 데 큰 도움이 됩니다.

 

더 자세한 내용은 Microsoft Docs에서 확인할 수 있습니다.

• INFORMATION_SCHEMA.TABLES: https://docs.microsoft.com/ko-kr/sql/relational-databases/system-information-schema-views/information-schema-tables-transact-sql
• INFORMATION_SCHEMA.COLUMNS: https://docs.microsoft.com/ko-kr/sql/relational-databases/system-information-schema-views/information-schema-columns-transact-sql
• INFORMATION_SCHEMA.ROUTINES: https://docs.microsoft.com/ko-kr/sql/relational-databases/system-information-schema-views/information-schema-routines-transact-sql
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE: https://docs.microsoft.com/ko-kr/sql/relational-databases/system-information-schema-views/information-schema-key-column-usage-transact-sql