SQL Server metadata

01. SQL Server 2012 architecture and configuration
    SQL Server editions
    SQL Server installation and tools
    SQL Server metadata
        Compatibility views
        Catalog views
        Dynamic Management Objects
        Other metadata
    Components of the SQL Server engine
        Protocols
        Query processor
        The storage engine
    SQL Server 2012 configuration
        Using SQL Server Configuration Manager
        Managing services
    SQL Server system configuration
        Operating system configuration
        Trace flags
        SQL Server configuration settings
    Conclusion

SQL Server metadata
SQL Server에서 사용 할 수 있는 모든 개체, 데이터 유형, 제약조건, 구성 옵션 및 자원에 관한 정보를 저장하는 테이블을 시스템 기본 테이블이라고 합니다..
SQL Server 2000까지는 이 테이블의 목록과 내용을 확인 할 수 있었지만 SQL Server 2005 이후로는 다음 쿼리로 목록만을 확인 할 수 있습니다.
USE master
SELECT name FROM sys.objects WHERE type_desc = 'SYSTEM_TABLE'
시스템 기본 테이블의 내용을 보려고 확인하려고
SELECT * FROM sysrscols
라는 쿼리를 실행하면 개체 이름이 잘못되었다는 에러가 발생합니다.
만약 꼭 보고 싶다면 DAC 연결을 사용하여 볼 수 있습니다.

SQL Server 2005 이후로는 시스템 기본 테이블의 내용을 확인 할 수 없기 때문에 호환성 뷰, 카다로그 뷰, DMO 라는 세가지 유형의 메타데이터를 제공합니다.

Compatibility views
SQL Server 2005이전에는 시스템 프로시져(예를 들어 sp_help)가 제공하지 않는 정보에 접근하기 위해서 시스템 테이블을 사용 했습니다만, SQL Server 2005이후로는 DAC를 통하지 않고서는 시스템 테이블의 내용을 볼 수 없습니다.
하지만 시스템 테이블에 익숙한 사용자, 또는 시스템 테이블에 기초하여 개발된 기존 프로그램들을 위해서 SQL Server 2005 이후 버전에서는 호환성 뷰를 제공합니다.
이름도 시스템 테이블과 크게 다르지 않습니다.
만약 SQL Server 2000에서 시스템 테이블인 sysobjects를 사요했다면 SQL Server 2005 이사에서는 호환성 뷰인 sysobjects를 사용할 수 있습니다.
sysobjects가 호환성 뷰인 이유는 이것이 뷰 이기 때문입니다.
sp_helptext sysobjects 를 사용하면 sysobjects(또는 다른 호환성 뷰)의 소스를 확인 할 수 있습니다.
호환성 뷰는 말 그대로 SQL Server 2000과의 호환성을 위해 제공 되는 것이기 때문에 앞으로는 카다로그 뷰나 DMO를 사용하기를 권장합니다. 호환성 뷰는 오직 호환성을 유지해야 할 경우에만 사용합니다. 프로그램을 고치기가 귀찮타던지.... 그런 경우...

Catalog views
카다로그 뷰는 SQL Server 2005이상에서 메타데이터에 접근 하기 위한 기본 인터페이스입니다.
호환성을 위해 또는 혼란을 피하기 위해 SQL Server 2000의 시스템 테이블과 유사한 이름을 제공합니다.
SELECT * FROM SYSDATABASES
SELECT * FROM SYS.DATABASES
카다로그 뷰가 더 많은 정보를 제공하는 것을 알 수 있습니다.
이전에는 DATABASE에 대한 정보를 확인하려면 sp_helpdb를 사용했지만
프로시져 이기 때문에 필터링은 불가능 했습니다.
하지만 카다로그 뷰를 사용하면 손쉽게 필터링이 가능합니다.
SELECT * FROM sys.databases WHERE recovery_model_desc = 'SIMPLE'
카다로그 뷰도 뷰 이기 때문에 소스를 확인 할 수 있습니다.
EXEC SP_HELPTEXT [sys.databases]
GO
SELECT object_definition(object_id('sys.databases'))
GO
SELECT definition FROM sys.system_sql_modules WHERE object_id = object_id('sys.databases')
sys.server_principals, sys.sql_logins, sys.databases 등은 master에만 존재하고 시스템 전체의 정보를 표시합니다.
sys.objects, sys.database_permissions, sys.server_permissions 등은 모든 database에 존재합니다.

시스템 테이블에 해당하는 카다로그 뷰를 확인하려면 다음 링크를 참고하세요.
시스템 테이블을 시스템 뷰로 매핑

Dynamic Management Objects
sys.dm_로 시작하는 메타데이터가 Dynamic Management Object입니다. 뷰와 펑션이 있는데 보통 DMV라고 부릅니다. DMV를 이용하면 SQL Server 내부 동작의 대부분을 관찰 할 수 있습니다.
DMV의 주요 범주는 다음과 같습니다.
dm_exec_* : 사용자 코드의 실행과 관련된 연결에 대한 정보가 포함되어 있습니다.
예를 들어서 sys.dm_exec_sessions는 SQL Server에서 인증된 세션당 하나의 행을 반환합니다. SQL Server 2000의 sysprocesses와 비슷하지만 더 많은 정보를 제공합니다.
dm_os_* : 메모리나 스케줄러에 관한 정보를 제공합니다.
dm_tran_* : 현재 트랜잭션에 대한 자세한 정보를 제공합니다.
dm_logpool* : SQL Server 2012의 로그 캐시를 관리하는데 사용되는 로그 풀에 대한 세부정보를 제공합니다. (2008R2 까지는 이 범주가 없음)
dm_io_* : 네트워크와 디스크의 입/출력 작업을 추적합니다.
dm_db_* : 데이터베이스와 데이터베이스 내의 개체, 예를 들면 인덱스 등에 대한 정보를 제공합니다.

이 밖에도 전체 텍스트 검색, 서비스 브로커, 복제, 가용성 그룹, TDE, 확장 이벤트 및 CLR을 모니터링 할 수 있는 DMV들이 있습니다.

Other metadata
이 밖에도 카다로그에 접근할 수 있는 몇가지 도구가 더 있습니다.
Information schema views
정보 스키마 뷰는 SQL-92 표준을 따르는 메타데이터입니다. 비표준인 인덱스, 파일그룹, CLR, 서비스 브로커 등에 대한 정보는 카다로그 뷰를 사용해야 합니다.
SQL-92 표준이기 때문에 다른 DBMS에서도 정보스키마 뷰를 사용할 수 있습니다.
예를 들어서 다음 쿼리는 MSSQL과 MySQL 모두에서 동작합니다.
select * From information_schema.tables
System functions
서버내의 개체들이나 데이터베이스 또는 인스턴스 자체에 대한 정보를 제공하는 함수들입니다.
SERVERPROPERTY
COLUMNPROPERTY
DATABASEPROPERTYEX
INDEXPROPERTY
INDEXKEY_PROPERTY
OBJECTPROPERTY
OBJECTPROPERTYEX
SQL_VARIANT_PROPERTY
FILEPROPERTY
FILEGROUPPROPERTY
FULLTEXTCATALOGPROPERTY
FULLTEXTSERVICEPROPERTY
TYPEPROPERTY
CONNECTIONPROPERTY
ASSEMBLYPROPERTY
카다로그 뷰를 통해서도 시스템 함수가 제공하는 값의 일부를 볼 수 있습니다. 예들 을어서 데이터베이스의 복구모델을 다음 두가지 방법으로 확인 할 수 있습니다.
SELECT DATABASEPROPERTYEX('msdb', 'Recovery');

SELECT name, recovery_model, recovery_model_desc FROM sys.databases WHERE name = 'msdb'
이 외에도 메타데이터에 접근하기 위한 함수들이 제공됩니다.
@@PROCID
INDEX_COL
APP_NAME
INDEXKEY_PROPERTY
APPLOCK_MODE
INDEXPROPERTY
APPLOCK_TEST
NEXT VALUE FOR
ASSEMBLYPROPERTY
OBJECT_DEFINITION
COL_LENGTH
OBJECT_ID
COL_NAME
OBJECT_NAME
COLUMNPROPERTY
OBJECT_SCHEMA_NAME
DATABASE_PRINCIPAL_ID
OBJECTPROPERTY
DATABASEPROPERTYEX
OBJECTPROPERTYEX
DB_ID
ORIGINAL_DB_NAME
DB_NAME
PARSENAME
FILE_ID
SCHEMA_ID
FILE_IDEX
SCHEMA_NAME
FILE_NAME
SCOPE_IDENTITY
FILEGROUP_ID
SERVERPROPERTY
FILEGROUP_NAME
STATS_DATE
FILEGROUPPROPERTY
TYPE_ID
FILEPROPERTY
TYPE_NAME
FULLTEXTCATALOGPROPERTY
TYPEPROPERTY
FULLTEXTSERVICEPROPERTY
System stored procedures
시스템 저장 프로시저는 메타데이터에 접근 하기 위한 오래된 방법입니다. SQL Server 2012에서도 당연히 작동합니다. 하지만 카다로그 뷰를 사용하는게 더 쉽습니다.
예를 들어서 sp_helpdb를 통해서는 특정 소유주의 데이터베이스 목록을 조회하거나 호환성 모드가 낮은 데이터베이스 목록을 조회하는게 불가능 하지만 카다로그 뷰로는 쉽게 할 수 있습니다.
SELECT name FROM sys.databases WHERE suser_sname(owner_sid) ='sue';
SELECT name FROM sys.databases WHERE compatibility_level < 110;
Metadata wrap-up
다음 그림은 Metadata 계층도입니다.

메타데이터에 접근하는 인터페이스중 가장 선호되는 것은 카다로그 뷰와 시스템 펑션입니다. 호환성뷰, 정보시키마 뷰, 시스템 프로시저는 실제로 카다로그 뷰를 기반으로 작성되진 않았지만 카다로그 뷰의 상위계층으로 생각하는것이 편합니다.