Appendix D: Information Schema Views Reference

Overview

The following views can be used in any database to obtain metadata about database objects. Select from each view as if it were a table in the database, prefixing the view with "INFORMATION_SCHEMA." as in the following example:

 SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS 

Information schema views are stored in the Master database. Note that the SysName(256) user-defined data type is equivalent to nVarChar(128).

Check_Constraints

Column Name	Data Type
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	SysName(256)
CHECK_CLAUSE	nVarChar(8000)

Column_Domain_Usage

Column Name	Data Type
DOMAIN_CATALOG	nVarChar(256)
DOMAIN_SCHEMA	nVarChar(256)
DOMAIN_NAME	SysName(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
COLUMN_NAME	SysName(256)
DOMAIN_CATALOG	nVarChar(256)

Column_Privileges

Column Name	Data Type
GRANTOR	nVarChar(256)
GRANTEE	nVarChar(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
COLUMN_NAME	SysName(256)
PRIVILEGE_TYPE	VarChar(10)
IS_GRANTABLE	VarChar(3)

Columns

Column Name	Data Type
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
COLUMN_NAME	SysName(256)
ORDINAL_POSITION	SmallInt(2)
COLUMN_DEFAULT	nVarChar(8000)
IS_NULLABLE	VarChar(3)
DATA_TYPE	SysName(256)
CHARACTER_MAXIMUM_LENGTH	Int(4)
CHARACTER_OCTET_LENGTH	Int(4)
NUMERIC_PRECISION	TinyInt(1)
NUMERIC_PRECISION_RADIX	SmallInt(2)
NUMERIC_SCALE	Int(4)
DATETIME_PRECISION	SmallInt(2)
CHARACTER_SET_CATALOG	nVarChar(256)
CHARACTER_SET_SCHEMA	nVarChar(256)
CHARACTER_SET_NAME	nVarChar(256)
COLLATION_CATALOG	nVarChar(256)
COLLATION_SCHEMA	nVarChar(256)
COLLATION_NAME	SysName(256)
DOMAIN_CATALOG	nVarChar(256)
DOMAIN_SCHEMA	nVarChar(256)
DOMAIN_NAME	nVarChar(256)

Constraint_Column_Usage

Column Name	Data Type
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	nVarChar(256)
COLUMN_NAME	nVarChar(256)
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	nVarChar(256)

Constraint_Table_Usage

Column Name	Data Type
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	SysName(256)

Domain_Constraints

Column Name	Data Type
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	SysName(256)
DOMAIN_CATALOG	nVarChar(256)
DOMAIN_SCHEMA	nVarChar(256)
DOMAIN_NAME	SysName(256)
IS_DEFERRABLE	VarChar(2)
INITIALLY_DEFERRED	VarChar(2)

Domains

Column Name	Data Type
DOMAIN_CATALOG	nVarChar(256)
DOMAIN_SCHEMA	nVarChar(256)
DOMAIN_NAME	SysName(256)
DATA_TYPE	SysName(256)
CHARACTER_MAXIMUM_LENGTH	Int(4)
CHARACTER_OCTET_LENGTH	Int(4)
COLLATION_CATALOG	nVarChar(256)
COLLATION_SCHEMA	nVarChar(256)
COLLATION_NAME	SysName(256)
CHARACTER_SET_CATALOG	nVarChar(256)
CHARACTER_SET_SCHEMA	nVarChar(256)
CHARACTER_SET_NAME	nVarChar(256)
NUMERIC_PRECISION	TinyInt(1)
NUMERIC_PRECISION_RADIX	SmallInt(2)
NUMERIC_SCALE	Int(4)
DATETIME_PRECISION	SmallInt(2)
DOMAIN_DEFAULT	nVarChar(8000)

Key_Column_Usage

Column Name	Data Type
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	nVarChar(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	nVarChar(256)
COLUMN_NAME	nVarChar(256)
ORDINAL_POSITION	Int(4)

Parameters

Column Name	Data Type
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	nVarChar(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	nVarChar(256)
COLUMN_NAME	nVarChar(256)
ORDINAL_POSITION	Int(4)

Referential_Constraints

Column Name	Data Type
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	SysName(256)
UNIQUE_CONSTRAINT_CATALOG	nVarChar(256)
UNIQUE_CONSTRAINT_SCHEMA	nVarChar(256)
UNIQUE_CONSTRAINT_NAME	SysName(256)
MATCH_OPTION	VarChar(4)
UPDATE_RULE	VarChar(9)
DELETE_RULE	VarChar(9)

Routine_Columns

Column Name	Data Type
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
COLUMN_NAME	SysName(256)
ORDINAL_POSITION	SmallInt(2)
COLUMN_DEFAULT	nVarChar(8000)
IS_NULLABLE	VarChar(3)
DATA_TYPE	SysName(256)
CHARACTER_MAXIMUM_LENGTH	Int(4)
CHARACTER_OCTET_LENGTH	Int(4)
NUMERIC_PRECISION	TinyInt(1)
NUMERIC_PRECISION_RADIX	SmallInt(2)
NUMERIC_SCALE	Int(4)
DATETIME_PRECISION	SmallInt(2)
CHARACTER_SET_CATALOG	nVarChar(256)
CHARACTER_SET_SCHEMA	nVarChar(256)
CHARACTER_SET_NAME	nVarChar(256)
COLLATION_CATALOG	nVarChar(256)
COLLATION_SCHEMA	nVarChar(256)
COLLATION_NAME	SysName(256)
DOMAIN_CATALOG	nVarChar(256)
DOMAIN_SCHEMA	nVarChar(256)
DOMAIN_NAME	nVarChar(256)

Routines

Column Name	Data Type
SPECIFIC_CATALOG	nVarChar(256)
SPECIFIC_SCHEMA	nVarChar(256)
SPECIFIC_NAME	nVarChar(256)
ROUTINE_CATALOG	nVarChar(256)
ROUTINE_SCHEMA	nVarChar(256)
ROUTINE_NAME	nVarChar(256)
ROUTINE_TYPE	nVarChar(40)
MODULE_CATALOG	nVarChar(256)
MODULE_SCHEMA	nVarChar(256)
MODULE_NAME	nVarChar(256)
UDT_CATALOG	nVarChar(256)
UDT_SCHEMA	nVarChar(256)
UDT_NAME	nVarChar(256)
DATA_TYPE	nVarChar(256)
CHARACTER_MAXIMUM_LENGTH	Int(4)
CHARACTER_OCTET_LENGTH	Int(4)
COLLATION_CATALOG	nVarChar(256)
COLLATION_SCHEMA	nVarChar(256)
COLLATION_NAME	nVarChar(256)
CHARACTER_SET_CATALOG	nVarChar(256)
CHARACTER_SET_SCHEMA	nVarChar(256)
CHARACTER_SET_NAME	nVarChar(256)
NUMERIC_PRECISION	TinyInt(1)
NUMERIC_PRECISION_RADIX	SmallInt(2)
NUMERIC_SCALE	Int(4)
DATETIME_PRECISION	SmallInt(2)
INTERVAL_TYPE	nVarChar(60)
INTERVAL_PRECISION	SmallInt(2)
TYPE_UDT_CATALOG	nVarChar(256)
TYPE_UDT_SCHEMA	nVarChar(256)
TYPE_UDT_NAME	nVarChar(256)
SCOPE_CATALOG	nVarChar(256)
SCOPE_SCHEMA	nVarChar(256)
SCOPE_NAME	nVarChar(256)
MAXIMUM_CARDINALITY	bigInt(8)
DTD_IDENTIFIER	nVarChar(256)
ROUTINE_BODY	nVarChar(60)
ROUTINE_DEFINITION	nVarChar(8000)
EXTERNAL_NAME	nVarChar(256)
EXTERNAL_LANGUAGE	nVarChar(60)
PARAMETER_STYLE	nVarChar(60)
IS_DETERMINISTIC	nVarChar(20)
SQL_DATA_ACCESS	nVarChar(60)
IS_NULL_CALL	nVarChar(20)
SQL_PATH	nVarChar(256)
SCHEMA_LEVEL_ROUTINE	nVarChar(20)
MAX_DYNAMIC_RESULT_SETS	SmallInt(2)
IS_USER_DEFINED_CAST	nVarChar(20)
IS_IMPLICITLY_INVOCABLE	nVarChar(20)
CREATED	DateTime(8)
LAST_ALTERED	DateTime(8)

Schemata

Column Name	Data Type
CATALOG_NAME	SysName(256)
SCHEMA_NAME	nVarChar(256)
SCHEMA_OWNER	nVarChar(256)
DEFAULT_CHARACTER_SET_CATALOG	nVarChar(256)
DEFAULT_CHARACTER_SET_SCHEMA	nVarChar(256)
DEFAULT_CHARACTER_SET_NAME	SysName(256)

Table_Constraints

Column Name	Data Type
CONSTRAINT_CATALOG	nVarChar(256)
CONSTRAINT_SCHEMA	nVarChar(256)
CONSTRAINT_NAME	SysName(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
CONSTRAINT_TYPE	VarChar(11)
IS_DEFERRABLE	VarChar(2)
INITIALLY_DEFERRED	VarChar(2)

Table_Privileges

Column Name	Data Type
GRANTOR	nVarChar(256)
GRANTEE	nVarChar(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
PRIVILEGE_TYPE	VarChar(10)
IS_GRANTABLE	VarChar(3)

Tables

Column Name	Data Type
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
TABLE_TYPE	VarChar(10)

View_Column_Usage

Column Name	Data Type
VIEW_CATALOG	nVarChar(256)
VIEW_SCHEMA	nVarChar(256)
VIEW_NAME	SysName(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)
COLUMN_NAME	SysName(256)

View_Table_Usage

Column Name	Data Type
VIEW_CATALOG	nVarChar(256)
VIEW_SCHEMA	nVarChar(256)
VIEW_NAME	SysName(256)
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	SysName(256)

Views

Column Name	Data Type
TABLE_CATALOG	nVarChar(256)
TABLE_SCHEMA	nVarChar(256)
TABLE_NAME	nVarChar(256)
VIEW_DEFINITION	nVarChar(8000)
CHECK_OPTION	VarChar(7)
IS_UPDATABLE	VarChar(2)