예를 들어 pubs 데이터베이스에는 어떠한 테이블들이 존재하는지 알고 싶은 경우 시스템 테이블을 참고하여 이 정보를 얻을 수 있습니다. 특히 시스템 테이블 중에서 sysobjects 테이블을 이용하면 됩니다.

USE pubs GO SELECT name FROM sysobjects WHERE xtype = 'U'

그러나!

sysobjects 라고 하는 시스템 테이블은 SQL 서버 차기 버젼에서 제공이 된다는 보장이 없습니다. 만일 차기 버젼에서 sysobjects 테이블이 제공되지 않는다면 위 쿼리문은 수행이 될 수 없습니다. MS에서는 이러한 문제를 해결하기 위하여 INFORMATION_SCHEMA 뷰를 제공하고 있습니다. INFORMATION_SCHEMA 뷰는 실제 테이블이 아니고 뷰(View) 이기 때문에 물리적인 시스템 테이블이 변경된다 하더라도 뷰를 사용하는 방법과 결과는 달라지지 않기 때문에 SQL 서버 차기 버젼에서도 사용 할 수 있습니다. 예를 들어 pubs 데이터베이스의 테이블을 확인하는 위 쿼리문은 다음과 같이 변경하여 사용할 수 있습니다.

USE pubs GO SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'

이 쿼리문에서는 INFORMATION_SCHEMA.TABLES 라고 하는 뷰를 사용하고 있습니다. 여기서 우리는 INFORMATION_SCHEMA가 무엇인지 예측 할 수 있습니다. SQL 서버에서는 개체를 표시 할 때 개체 앞에 그 개체의 소유자를 지정하게 되어 있습니다. 물론 대부분 생략을 하고 사용합니다. INFORMATION_SCHEMA.TABLES 에서 뒷부분의 TABLES가 뷰 개체일 것이며 그 앞의 INFORMATION_SCHEMA 는 이 개체의 소유자가 됩니다. 이 것은 다음 [그림 1] 에서 확인할 수 있습니다.

[그림 1]

[그림 1]에서 보면 "소유자" 부분에 INFORMATION_SCHEMA로 되어 있는 것을 볼 수 있습니다. 그리고 관련된 뷰가 20개 정도 되는 것을 알 수 있습니다. 우리는 이 20개의 뷰를 이용해서 필요한 정보를 얻을 수 있습니다. 이들 뷰 중에서 많이 사용되는 몇가지 예를 살펴보도록 하겠습니다.

1. 테이블 정보 확인하기 : INFORMATION_SCHEMA.TABLES

앞에서 살펴본 것처럼 INFORMATION_SCHEMA.TABLES 를 이용하면 현재 데이터베이스에 속한 테이블에 대한 정보를 얻을 수 있습니다. 만일 다음과 같이 수행하게 되면 테이블과 뷰가 함께 표시됩니다.

USE pubs GO SELECT * FROM INFORMATION_SCHEMA.TABLES

표시되는 것 중에서 일반적인 테이블은 table_type가 'BASE TABLE' 이며 뷰의 경우는 'VIEW' 입니다. 그래서 만일 뷰가 아닌 테이블만 보고 싶으면 다음과 같이 조건을 주면 됩니다.

USE pubs GO SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE'

만일 't' 로 시작되는 테이블의 이름을 얻고 싶다면 어떻게 하면 될까요? 다음과 같이 하시면 됩니다.

USE pubs GO SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' AND table_name LIKE 't%'

2. 뷰(View) 정보 확인하기 : INFORMATION_SCHEMA.VIEWS

INFORMATION_SCHEMA.VIEWS 는 현재 데이터베이스에 속한 뷰에 대한 정보를 제공해 줍니다. 내부적으로 sysobjects와 syscomments 시스템 테이블을 이용하여 정보를 제공해주는 것입니다.

만일 pubs 데이터베이스의 titleview에 대한 정보를 얻고 싶으면 다음과 같이 하시면 됩니다.

USE pubs GO SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'titleview'

표시되는 내용에는 VIEW_DEFINITION 컬럼이 있는데 이 컬럼에는 CREATE VIEW... 가 포함되어 이 뷰가 어떠한 내용을 갖고 있는지 알 수 있습니다. 뷰의 실제 내용을 볼 때 sp_helptext 서장 프로시져를 사용 할 수도 있지만 다음과 같이 해도 된다는 것입니다.

USE pubs GO SELECT view_definition FROM INFORMATION_SCHEMA.VIEWS WHERE table_name = 'titleview'

3. 컬럼 정보 확인하기 : INFORMATION_SCHEMA.COLUMNS

현재 데이터베이스에서 현재 사용자가 액세스할 수 있는 각 열에 대한 정보를 얻을 수 있습니다. 뷰 내부적으로는 sysobjects, spt_datatype_info, systypes, syscolumns, syscomments, sysconfigures, syscharsets 과 같이 여러가지 시스템 테이블을 사용하고 있습니다. 이런 여러가지 시스템 테이블들에 대해서 알지 못해도 우리는 INFORMATION_SCHEMA.COLUMNS 만 검색하면 되는 것입니다.

다음의 쿼리문은 titles 테이블의 컬럼과 그 컬럼의 NULL 여부, 컬럼 형식이 무엇인지 표시해 줍니다.

USE pubs GO SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'titles'

4. 정리

이 강좌에서는 많이 사용되는 뷰 몇 가지만 살펴 보았습니다. 하지만 이들 말고도 더 많은 INFORMATION_SCHEMA 뷰가 있습니다. 온라인 설명서에서 'INFORMATION_SCHEMA'를 찾아보시면 각 뷰들에 대한 설명을 보실 수 있습니다. 알고 있으면 참 편한데 알지 못하기 때문에 고생하는 경우가 많습니다. 어려운 시스템 테이블들을 아는 것고 좋지만 우선 INFORMATION_SCHEMA 뷰 들에 대한 내용을 알고 있는게 더 중요하다고 생각합니다.

EM(Enterprise Manager)에서 master 데이터베이스에 있는 INFORMATION_SCHEMA 뷰를 더블클릭하시면 이 뷰가 어떻게 만들어 졌는지 볼 수 있습니다. 이 내용을 참고하시면 시스템 테이블들을 한층 더 이해할 수 있습니다.

 

출처 : http://www.sqlworld.pe.kr