SQL Server 시스템 카탈로그 쿼리
특정 테이블 및 뷰 집합에서 작동하도록 하드 코딩되지 않은 동적 응용 프로그램은 연결되는 모든 데이터베이스에서 개체의 구조와 특성을 확인할 수 있는 방법이 있어야 합니다. 이러한 응용 프로그램에는 다음과 같은 정보가 필요할 수 있습니다.
데이터베이스에 있는 테이블 및 뷰의 개수와 이름
테이블이나 뷰의 열 개수와 각 열의 이름, 데이터 형식, 소수 자릿수 및 전체 자릿수
테이블에 정의된 제약 조건
테이블에 정의된 인덱스와 키
시스템 카탈로그는 이러한 SQL Server 데이터베이스 정보를 제공합니다. SQL Server 시스템 카탈로그에서 가장 중요한 것은 SQL Server 인스턴스의 개체를 설명하는 메타데이터가 표시된 뷰 집합입니다. 메타데이터는 시스템에서 개체의 특성을 설명하는 데이터입니다. SQL Server 기반 응용 프로그램에서는 다음을 사용하여 시스템 카탈로그 정보에 액세스할 수 있습니다.
카탈로그 뷰. 이것은 권장되는 액세스 방법입니다.
정보 스키마 뷰
OLE DB 스키마 행 집합
ODBC 카탈로그 함수
시스템 저장 프로시저와 함수
카탈로그 뷰
카탈로그 뷰를 사용하면 서버의 모든 데이터베이스에 저장된 메타데이터에 액세스할 수 있습니다.
[!참고]
카탈로그 뷰에서는 복제, SQL Server 에이전트 또는 백업 메타데이터에 액세스할 수 없습니다.
메타데이터 액세스 방법으로 카탈로그 뷰가 권장되는 이유는 다음과 같습니다.
모든 메타데이터를 카탈로그 뷰로 사용할 수 있습니다.
카탈로그 뷰는 카탈로그 테이블 구현과는 다른 독립적인 형식으로 메타데이터를 제공하므로 기본 카탈로그 테이블이 변경되더라도 영향을 받지 않습니다.
카탈로그 뷰는 핵심 서버 메타데이터에 액세스하는 가장 효율적인 방법입니다.
카탈로그 뷰는 카탈로그 메타데이터를 표시하는 일반적인 인터페이스로서 이러한 메타데이터를 사용자 지정 형식으로 가져오고 변환하고 나타내는 가장 빠른 방법을 제공합니다.
카탈로그 뷰 이름 및 열 이름에는 설명이 포함됩니다. 따라서 쿼리할 메타데이터에 해당하는 기능에 대해 일정 수준의 지식을 갖춘 사용자라면 쉽게 원하는 쿼리 결과를 얻을 수 있습니다.
예를 들어 다음 쿼리는 sys.objects 카탈로그 뷰를 사용하여 최근 10일 동안 수정된 데이터베이스 개체를 모두 반환합니다.
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - 10
ORDER BY modify_date;
카탈로그 뷰를 사용하여 시스템 카탈로그를 쿼리하는 다른 예를 보려면 SQL Server 시스템 카탈로그 쿼리에 대한 질문과 대답을 참조하십시오.
.gif "중요 정보") 중요 |
|---|
이후 SQL Server 릴리스에서 Microsoft는 열 목록의 끝에 열을 추가하여 시스템 카탈로그 뷰의 정의를 보강할 수 있습니다. 반환된 열 수가 응용 프로그램을 바꾸거나 중단할 수 있으므로 프로덕션 코드의 SELECT * FROM sys.catalog_view_name 구문을 사용하는 것이 좋습니다. |
정보 스키마 뷰
정보 스키마 뷰는 ISO 표준의 카탈로그 뷰 정의를 기반으로 합니다. 정보 스키마 뷰는 카탈로그 테이블 구현과는 다른 독립적인 형식으로 카탈로그 정보를 제공하므로 기본 카탈로그 테이블이 변경되더라도 영향을 받지 않습니다. 이러한 뷰를 사용하는 응용 프로그램은 다른 유형의 ISO 규격 데이터베이스 시스템에 이식이 가능합니다. 자세한 내용은 정보 스키마 뷰(Transact-SQL)를 참조하십시오.
[!참고]
정보 스키마 뷰에는 SQL Server 2008에서만 사용할 수 있는 메타데이터는 포함되지 않습니다.
다음 예에서는 INFORMATION_SCHEMA.COLUMNS 뷰를 쿼리하여 AdventureWorks 데이터베이스에 있는 Contact 테이블의 모든 열을 반환합니다.
SELECT TABLE_NAME, COLUMN_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';
호환성 뷰
SQL Server의 이전 버전에 사용되었던 여러 시스템 테이블이 이제는 뷰 집합으로 구현됩니다. 이러한 뷰를 호환성 뷰라고 하며 이전 버전과의 호환성을 위해서만 제공됩니다. 호환성 뷰는 SQL Server 2000에서 사용되었던 것과 동일한 메타데이터를 표시합니다. 그러나 SQL Server 2005 이상에서 새로 추가된 기능과 관련된 메타데이터는 표시하지 않습니다. 따라서 Service Broker 또는 분할과 같은 새로운 기능을 사용할 때는 카탈로그 뷰로 전환하여 사용해야 합니다. 이러한 이유로 카탈로그 뷰로 업그레이드하는 것이 좋습니다. 카탈로그 뷰로 업그레이드해야 하는 또 다른 이유는 사용자 ID와 유형 ID를 저장하는 호환성 뷰 열에서 NULL이 반환되거나 산술 오버플로가 발생할 수 있다는 점입니다. 이것은 SQL Server 2005 이상에서 사용자 및 데이터 형식을 32,768개 이상 만들 수 있기 때문입니다. 예를 들어 32,768명의 사용자를 만들고 SELECT * FROM sys.sysusers와 같은 쿼리를 실행할 때 ARITHABORT를 ON으로 설정하는 경우 산술 오버플로 오류가 발생하여 쿼리가 실패하게 되고 ARITHABORT를 OFF로 설정하는 경우 uid 열에서 NULL이 반환됩니다.
이러한 문제를 방지하려면 대량의 사용자 ID와 유형 ID를 처리할 수 있는 새로운 카탈로그 뷰로 업그레이드하여 사용하는 것이 좋습니다.
OLE DB 스키마 행 집합
OLE DB 사양은 카탈로그 정보가 포함된 스키마 행 집합을 표시하는 IDBSchemaRowset 인터페이스를 정의합니다. OLE DB 스키마 행 집합은 여러 OLE DB 공급자가 지원하는 카탈로그 정보를 나타내는 표준 방식입니다. 행 집합은 기본 카탈로그 테이블의 구조와는 독립적입니다. 자세한 내용은 스키마 행 집합 지원(OLE DB)를 참조하십시오.
Microsoft SQL Server Native Client OLE DB 공급자는 분산 쿼리에 사용되는 연결된 서버의 카탈로그 정보를 보고하는 IDBSchemaRowset 확장을 지원합니다. 자세한 내용은 LINKEDSERVERS 행 집합(OLE DB)을 참조하십시오.
ODBC 카탈로그 함수
ODBC 사양은 카탈로그 정보가 포함된 결과 집합을 반환하는 카탈로그 함수 집합을 정의합니다. 이러한 함수는 여러 ODBC 드라이버가 지원하는 카탈로그 정보를 나타내는 표준 방식입니다. 결과 집합은 기본 카탈로그 테이블의 구조와는 독립적입니다.
SQL Server Native Client ODBC 드라이버는 분산 쿼리에 사용되는 연결된 서버의 카탈로그 정보를 보고하는 두 개의 드라이버별 함수를 지원합니다. 자세한 내용은 카탈로그 함수 사용를 참조하십시오.
시스템 저장 프로시저와 함수
Transact-SQL은 카탈로그 정보를 반환하는 서버 시스템 저장 프로시저와 시스템 함수를 정의합니다. 이러한 저장 프로시저와 함수는 SQL Server에서만 사용되지만 이 저장 프로시저와 함수는 기본 시스템 카탈로그 테이블의 구조에서 사용자를 분리합니다. 자세한 내용은 메타데이터 함수(Transact-SQL) 및 시스템 저장 프로시저(Transact-SQL)를 참조하십시오.