Beginning Transact-SQL With SQL Server 2000 and 2005
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) |
Категории