Understanding the Provider-Specific Schema Collections 

  • Article

The provider-specific schema collections are the schema collections that are specific to each of the .NET Framework managed providers. You can query a .NET Framework managed provider to determine the list of supported schema collections by calling the GetSchema method with no arguments, or with the schema collection name "MetaDataCollections". This will return a DataTable with a list of the supported schema collections, the number of restrictions that they each support, and the number of identifier parts that they use.

For more information about using the GetSchema methods, see Working with the GetSchema Methods.

The following sections list different drivers and providers along with the specific schema collections that they support.

Microsoft .NET Framework Data Provider for SQL Server

The Microsoft .NET Framework Data Provider for SQL Server supports the following specific schema collections in addition to the common schema collections.

  • Databases

  • ForeignKeys

  • Indexes

  • IndexColumns

  • Procedures

  • ProcedureParameters

  • Tables

  • Columns

  • Users

  • Views

  • ViewColumns

  • UserDefinedTypes

Databases

ColumnName DataType Description

database_name

String

Name of the database.

Dbid

Int16

Database ID.

create_date

DateTime

Creation Date of the database.

Foreign Keys

ColumnName DataType Description

constraint_catalog

String

Catalog the constraint belongs to.

constraint_schema

String

Schema that contains the constraint.

constraint_name

String

Name.

table_catalog

String

Table Name constraint is part of.

table_schema

String

Schema that that contains the table.

table_name

String

Table Name

constraint_type

String

Type of constraint. Only "FOREIGN KEY" is allowed.

is_deferrable

String

Specifies whether the constraint is deferrable. Returns NO.

initially_deferred

String

Specifies whether the constraint is initially deferrable. Returns NO.

Indexes

ColumnName DataType Description

constraint_catalog

String

Catalog that index belongs to.

constraint_schema

String

Schema that contains the index.

constraint_name

String

Name of the index.

table_catalog

String

Table name the index is associated with.

table_schema

String

Schema that contains the table the index is associated with.

table_name

String

Table Name.

IndexColumns

ColumnName DataType Description

constraint_catalog

String

Catalog that index belongs to.

constraint_schema

String

Schema that contains the index.

constraint_name

String

Name of the index.

table_catalog

String

Table name the index is associated with.

table_schema

String

Schema that contains the table the index is associated with.

table_name

String

Table Name.

column_name

String

Column name the index is associated with.

ordinal_position

Int32

Column ordinal position.

KeyType

UInt16

The type of object.

Procedures

This collection is only available in SQL Server 2000 and above.

ColumnName DataType Description

specific_catalog

String

Specific name for the catalog.

specific_schema

String

Specific name of the schema.

specific_name

String

Specific name of the catalog.

routine_catalog

String

Catalog the stored procedure belongs to.

routine_schema

String

Schema that contains the stored procedure.

routine_name

String

Name of the stored procedure.

routine_type

String

Returns PROCEDURE for stored procedures and FUNCTION for functions.

created

DateTime

Time the procedure was created.

last_altered

DateTime

The last time the procedure was modified.

Procedure Parameters

ColumnName DataType Description

specific_catalog

String

Catalog name of the procedure for which this is a parameter.

specific_schema

String

Schema that contains the procedure for which this parameter is part of.

specific_name

String

Name of the procedure for which this parameter is a part of.

ordinal_position

Int16

Ordinal position of the parameter starting at 1. For the return value of a procedure, this is a 0.

parameter_mode

String

Returns IN if an input parameter, OUT if an output parameter, and INOUT if an input/output parameter.

is_result

String

Returns YES if indicates result of the procedure that is a function. Otherwise, returns NO.

as_locator

String

Returns YES if declared as locator. Otherwise, returns NO.

parameter_name

String

Name of the parameter. NULL if this corresponds to the return value of a function.

data_type

String

System-supplied data type.

character_maximum_length

Int32

Maximum length in characters for binary or character data types. Otherwise, returns NULL.

character_octet_length

Int32

Maximum length, in bytes, for binary or character data types. Otherwise, returns NULL.

collation_catalog

String

Catalog name of the collation of the parameter. If not one of the character types, returns NULL.

collation_schema

String

Always returns NULL.

collation_name

String

Name of the collation of the parameter. If not one of the character types, returns NULL.

character_set_catalog

String

Catalog name of the character set of the parameter. If not one of the character types, returns NULL.

character_set_schema

String

Always returns NULL.

character_set_name

String

Name of the character set of the parameter. If not one of the character types, returns NULL.

numeric_precision

Byte

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

numeric_precision_radix

Int16

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

numeric_scale

Int32

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, returns NULL.

datetime_precision

Int16

Precision in fractional seconds if the parameter type is datetime or smalldatetime. Otherwise, returns NULL.

interval_type

String

NULL. Reserved for future use by SQL Server.

interval_precision

Int16

NULL. Reserved for future use by SQL Server.

Tables

ColumnName DataType Description

table_catalog

String

Catalog of the table.

table_schema

String

Schema that contains the table.

table_name

String

Table name.

table_type

String

Type of table. Can be VIEW or BASE TABLE.

Columns

ColumnName DataType Description

table_catalog

String

Catalog of the table.

table_schema

String

Schema that contains the table.

table_name

String

Table name.

column_name

String

Column name.

ordinal_position

Int16

Column identification number.

column_default

String

Default value of the column

is_nullable

String

Nullability of the column. If this column allows NULL, this column returns YES. Otherwise, No is returned.

data_type

String

System-supplied data type.

character_maximum_length

Int32 – Sql8, Int16 – Sql7

Maximum length, in characters, for binary data, character data, or text and image data. Otherwise, NULL is returned.

character_octet_length

Int32 – SQL8, Int16 – Sql7

Maximum length, in bytes, for binary data, character data, or text and image data. Otherwise, NULL is returned.

numeric_precision

Unsigned Byte

Precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_precision_radix

Int16

Precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

numeric_scale

Int32

Scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, NULL is returned.

datetime_precision

Int16

Subtype code for datetime and SQL-92 interval data types. For other data types, NULL is returned.

character_set_catalog

String

Returns master, indicating the database in which the character set is located, if the column is character data or text data type. Otherwise, NULL is returned.

character_set_schema

String

Always returns NULL.

character_set_name

String

Returns the unique name for the character set if this column is character data or text data type. Otherwise, NULL is returned.

collation_catalog

String

Returns master, indicating the database in which the collation is defined, if the column is character data or text data type. Otherwise, this column is NULL.

Users

ColumnName DataType Description

uid

Int16

User ID, unique in this database. 1 is the database owner.

name

String

Username or group name, unique in this database.

createdate

DateTime

Date the account was added.

updatedate

DateTime

Date the account was last changed.

Views

This collection is only available in SQL Server 2000 and above.

ColumnName DataType Description

table_catalog

String

Catalog of the view.

table_schema

String

Schema that contains the view.

table_name

String

View name.

check_option

String

Type of WITH CHECK OPTION. Is CASCADE if the original view was created using the WITH CHECK OPTION. Otherwise, NONE is returned.

is_updatable

String

Specifies whether the view is updatable. Always returns NO.

ViewColumns

ColumnName DataType Description

view_catalog

String

Catalog of the view.

view_schema

String

Schema that contains the view.

view_name

String

View name.

table_catalog

String

Catalog of the table that is associated with this view.

table_schema

String

Schema that contains the table that is associated with this view.

table_name

String

Name of the table that is associated with the view. Base Table.

column_name

String

Column name.

UserDefinedTypes

This collection is only available in SQL Server 2005 and above.

ColumnName DataType Description

assembly_name

String

The name of the file for the assembly.

UDT_name

String

The class name for the assembly.

version_major

Object

Major Version Number.

version_minor

Object

Minor Version Number.

version_build

Object

Build Number.

version_revision

Object

Revision Number.

Culture_info

Object

The culture information associated with this UDT.

Public_key

Object

The public key used by this Assembly.

Is_fixed_length

Boolean

Specifies whether length of type is always same as max_length.

max_length

Int16

Maximum length of type in bytes.

permission_set_desc

String

The friendly name for the permission-set/security-level for the assembly.

create_date

DateTime

The date the assembly was created/registered.

Microsoft .NET Framework Data Provider for Oracle

The Microsoft .NET Framework Data Provider for Oracle supports the following specific schema collections in addition to the common schema collections:

  • Columns

  • Indexes

  • IndexColumns

  • Procedures

  • Sequences

  • Synonyms

  • Tables

  • Users

  • Views

  • Functions

  • Packages

  • PackageBodies

  • Arguments

  • UniqueKeys

  • PrimaryKeys

  • ForeignKeys

  • ForeignKeyColumns

  • ProcedureParameters

Columns

ColumnName DataType Description

OWNER

String

Owner of the table, view or cluster.

TABLE_NAME

String

Table, view, or cluster name.

COLUMN_NAME

String

Column name.

ID

Decimal

Sequence number of the column as created.

DATATYPE

String

Datatype of the column.

LENGTH

Decimal

Length of the column in bytes.

PRECISION

Decimal

Decimal precision for NUMBER datatype; binary precision for FLOAT datatype, null for all other datatypes.

SCALE

Decimal

Digits to right of decimal point in a number.

NULLABLE

String

Specifies whether a column allows NULLs. Value is N if there is a NOT NULL constraint on the column or if the column is part of a PRIMARY KEY.

Indexes

ColumnName DataType Description

OWNER

String

Owner of the index

INDEX_NAME

String

Name of the index.

INDEX_TYPE

String

Type of index (NORMAL, BITMAP, FUNCTION-BASED NORMAL, FUNCTION-BASED BITMAP, or DOMAIN).

TABLE_OWNER

String

Owner of the indexed object.

TABLE_NAME

String

Name of the indexed object.

TABLE_TYPE

String

Type of the indexed object (for example, TABLE, CLUSTER).

UNIQUENESS

String

Whether the index is UNIQUE or NONUNIQUE.

COMPRESSION

String

Whether the index is ENABLED or DISABLED.

PREFIX_LENGTH

Decimal

Number of columns in the prefix of the compression key.

TABLESPACE_NAME

String

Name of the tablespace containing the index.

INI_TRANS

Decimal

Initial number of transactions.

MAX_TRANS

Decimal

Maximum number of transactions.

INITIAL_EXTENT

Decimal

Size of the initial extent.

NEXT_EXTENT

Decimal

Size of secondary extents.

MIN_EXTENTS

Decimal

Minimum number of extents allowed in the segment.

MAX_EXTENTS

Decimal

Maximum number of extents allowed in the segment.

PCT_INCREASE

Decimal

Percentage increase in extent size.

PCT_THRESHOLD

Decimal

Threshold percentage of block space allowed per index entry.

INCLUDE_COLUMN

Decimal

Column ID of the last column to be included in index-organized table primary key (non-overflow) index. This column maps to the COLUMN_ID column of the *_TAB_COLUMNS data dictionary views.

FREELISTS

Decimal

Number of process freelists allocated to this segment.

FREELIST_GROUPS

Decimal

Number of freelist groups allocated to this segment.

PCT_FREE

Decimal

Minimum percentage of free space in a block.

LOGGING

String

Logging information.

BLEVEL

Decimal

B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same.

LEAF_BLOCKS

Decimal

Number of leaf blocks in the index

DISTINCT_KEYS

Decimal

Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS).

AVG_LEAF_BLOCKS_PER_KEY

Decimal

Average number of leaf blocks in which each distinct value in the index appears rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.

AVG_DATA_BLOCKS_PER_KEY

Decimal

Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed columns.

CLUSTERING_FACTOR

Decimal

Indicates the amount of order of the rows in the table based on the values of the index.

STATUS

String

Whether a nonpartitioned index is VALID or UNUSABLE.

NUM_ROWS

Decimal

Number of rows in the index.

SAMPLE_SIZE

Decimal

Size of the sample used to analyze the index.

LAST_ANALYZED

DateTime

Date on which this index was most recently analyzed.

DEGREE

String

Number of threads per instance for scanning the index.

INSTANCES

String

Number of instances across which the indexes to be scanned.

PARTITIONED

String

Whether this index is partitioned (YES | NO).

TEMPORARY

String

Whether the index is on a temporary table.

GENERATED

String

Whether the name of the index is system generated (Y|N).

SECONDARY

String

Whether the index is a secondary object created by the ODCIIndexCreate method of the Oracle9i Data Cartridge (Y|N).

BUFFER_POOL

String

Name of the default buffer pool to be used for the index blocks.

USER_STATS

String

Whether the statistics were entered directly by the user.

DURATION

String

Indicates the duration of a temporary table: 1)SYS$SESSION: the rows are preserved for the duration of the session, 2) SYS$TRANSACTION: the rows are deleted after COMMIT, 3) Null for permanent Table

PCT_DIRECT_ACCESS

Decimal

For a secondary index on an index-organized table, the percentage of rows with VALID guess

ITYP_OWNER

String

For a domain index, the owner of the indextype.

ITYP_NAME

String

For a domain index, the name of the indextype.

PARAMETERS

String

For a domain index, the parameter string.

GLOBAL_STATS

String

For partitioned indexes, indicates whether statistics were collected by analyzing index as a whole (YES) or were estimated from statistics on underlying index partitions and subpartitions (NO).

DOMIDX_STATUS

String

Reflects the status of the domain index. NULL: the specified index is not a domain index. VALID: the index is a valid domain index. IDXTYP_INVLD: the index type of this domain index is invalid.

DOMIDX_OPSTATUS

String

Reflects the status of an operation that was performed on a domain index: NULL: the specified index is not a domain index. VALID: the operation performed without errors. FAILED: the operation failed with an error.

FUNCIDX_STATUS

String

Indicates the status of a function-based index: NULL: this is not a function-based index, ENABLED: the function-based index is enabled, DISABLED: the function-based index is disabled.

JOIN_INDEX

String

Indicates whether this is a join index or not.

IndexColumns

ColumnName DataType Description

INDEX_OWNER

String

Owner of the index.

INDEX_NAME

String

Name of the index.

TABLE_OWNER

String

Owner of the table or cluster.

TABLE_NAME

String

Name of the table or cluster.

COLUMN_NAME

String

Column name or attribute of object type column.

COLUMN_POSITION

Decimal

Position of column or attribute within the index.

COLUMN_LENGTH

Decimal

Indexed length of the column.

CHAR_LENGTH

Decimal

Maximum codepoint length of the column.

DESCEND

String

Whether the column is sorted in descending order.

Procedures

ColumnName DataType Description

OWNER

String

Owner of the object.

OBJECT_NAME

String

Name of the object.

SUBOBJECT_NAME

String

Name of the subobject (for example, partition).

OBJECT_ID

Decimal

Dictionary object number of the object.

DATA_OBJECT_ID

Decimal

Dictionary object number of the segment that contains the object.

LAST_DDL_TIME

DateTime

Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes).

TIMESTAMP

String

Timestamp for the specification of the object (character data).

STATUS

String

Status of the object (VALID, INVALID, or N/A).

TEMPORARY

String

Whether the object is temporary (the current session can see only data that it placed in this object itself).

GENERATED

String

Was the name of this object system generated? (Y | N).

SECONDARY

String

Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle9i Data Cartridge (Y | N).

CREATED

DateTime

The date the object was created.

Sequences

ColumnName DataType Description

SEQUENCE_OWNER

String

Name of the owner of the sequence.

SEQUENCE_NAME

String

Sequence name.

MIN_VALUE

Decimal

Minimum value of the sequence.

MAX_VALUE

Decimal

Maximum value of the sequence.

INCREMENT_BY

Decimal

Value by which sequence is incremented.

CYCLE_FLAG

String

Does sequence wrap around on reaching limit.

ORDER_FLAG

String

Are sequence numbers generated in order.

CACHE_SIZE

Decimal

Number of sequence numbers to cache.

LAST_NUMBER

Decimal

Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.

Synonyms

ColumnName DataType Description

OWNER

String

Owner of the synonym.

SYNONYM_NAME

String

Name of the synonym.

TABLE_OWNER

String

Owner of the object referenced by the synonym.

TABLE_NAME

String

Name of the object referenced by the synonym.

DB_LINK

String

Name of the database link referenced, if any.

Tables

ColumnName DataType Description

OWNER

String

Owner of the table.

TABLE_NAME

String

Name of the table.

TYPE

String

Type of table.

Users

ColumnName DataType Description

NAME

String

Name of the user.

ID

Decimal

ID number of the user.

CREATEDATE

DateTime

User creation date.

Views

ColumnName DataType Description

OWNER

String

Owner of the view.

VIEW_NAME

String

Name of the view.

TEXT_LENGTH

Decimal

Length of the view text.

TEXT

String

View text.

TYPE_TEXT_LENGTH

Decimal

Length of the type clause of the typed view.

TYPE_TEXT

String

Type clause of the typed view.

OID_TEXT_LENGTH

Decimal

Length of the WITH OID clause of the typed view.

OID_TEXT

String

WITH OID clause of the typed view.

VIEW_TYPE_OWNER

String

Owner of the type of the view if the view is a typed view.

VIEW_TYPE

String

Type of the view if the view is a typed view.

SUPERVIEW_NAME

String

Name of the superview.

Functions

ColumnName DataType Description

OWNER

String

Owner of the object.

OBJECT_NAME

String

Name of the object.

SUBOBJECT_NAME

String

Name of the subobject (for example, partition).

OBJECT_ID

Decimal

Dictionary object number of the object.

DATA_OBJECT_ID

Decimal

Dictionary object number of the segment that contains the object.

OBJECT_TYPE

String

Type of the object.

CREATED

DateTime

The date the object was created.

LAST_DDL_TIME

DateTime

Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes).

TIMESTAMP

String

Timestamp for the specification of the object (character data)

STATUS

String

Status of the object (VALID, INVALID, or N/A).

TEMPORARY

String

Whether the object is temporary (the current session can see only data that it placed in this object itself).

GENERATED

String

Was the name of this object system generated? (Y | N).

SECONDARY

String

Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle9i Data Cartridge (Y | N).

Packages

ColumnName DataType Description

OWNER

String

Owner of the object.

OBJECT_NAME

String

Name of the object.

SUBOBJECT_NAME

String

Name of the subobject (for example, partition).

OBJECT_ID

Decimal

Dictionary object number of the object.

DATA_OBJECT_ID

Decimal

Dictionary object number of the segment that contains the object.

LAST_DDL_TIME

DateTime

Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes).

TIMESTAMP

String

Timestamp for the specification of the object (character data).

STATUS

String

Status of the object (VALID, INVALID, or N/A).

TEMPORARY

String

Whether the object is temporary (the current session can see only data that it placed in this object itself).

GENERATED

String

Was the name of this object system generated? (Y | N).

SECONDARY

String

Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle9i Data Cartridge (Y | N).

CREATED

DateTime

The date the object was created.

PackageBodies

ColumnName DataType Description

OWNER

String

Owner of the object.

OBJECT_NAME

String

Name of the object.

SUBOBJECT_NAME

String

Name of the subobject (for example, partition).

OBJECT_ID

Decimal

Dictionary object number of the object.

DATA_OBJECT_ID

Decimal

Dictionary object number of the segment that contains the object.

LAST_DDL_TIME

DateTime

Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes).

TIMESTAMP

String

Timestamp for the specification of the object (character data).

STATUS

String

Status of the object (VALID, INVALID, or N/A).

TEMPORARY

String

Whether the object is temporary (the current session can see only data that it placed in this object itself).

GENERATED

String

Was the name of this object system generated? (Y | N).

SECONDARY

String

Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle9i Data Cartridge (Y | N).

CREATED

DateTime

The date the object was created.

Arguments

ColumnName DataType Description

OWNER

String

Name of the owner of the object.

PACKAGE_NAME

String

Package name.

OBJECT_NAME

String

Name of the procedure or function.

ARGUMENT_NAME

String

Name of the argument.

POSITION

Decimal

Position in argument list, or NULL for function return value.

SEQUENCE

Decimal

Argument sequence, including all nesting levels.

DEFAULT_VALUE

String

Default value for the argument.

DEFAULT_LENGTH

Decimal

Length of default value for the argument.

IN_OUT

String

Argument direction (IN, OUT, or IN/OUT).

DATA_LENGTH

Decimal

Length of the column in bytes.

DATA_PRECISION

Decimal

Length in decimal digits (NUMBER) or binary digits (FLOAT).

DATA_SCALE

Decimal

Digits to right of decimal point in a number.

DATA_TYPE

String

Data type of the argument.

UniqueKeys

ColumnName DataType Description

OWNER

String

Owner of the constraint definition.

CONSTRAINT_NAME

String

Name of the constraint definition.

TABLE_NAME

String

Name associated with the table (or view) with constraint definition.

SEARCH_CONDITION

String

Text of search condition for a check constraint.

R_OWNER

String

Owner of table referred to in a referential constraint.

R_CONSTRAINT_NAME

String

Name of the unique constraint definition for referenced table.

DELETE_RULE

String

Delete rule for a referential constraint (CASCADE or NO ACTION).

STATUS

String

Enforcement status of constraint (ENABLED or DISABLED).

DEFERRABLE

String

Whether the constraint is deferrable.

VALIDATED

String

Whether all data obeys the constraint (VALIDATED or NOT VALIDATED).

GENERATED

String

Whether the name of the constraint is user or system generated.

BAD

String

A YES value indicates that this constraint specifies a century in an ambiguous manner. To avoid errors resulting from this ambiguity, rewrite the constraint using the TO_DATE function with a four-digit year.

RELY

String

Whether an enabled constraint is enforced or unenforced.

LAST_CHANGE

DateTime

When the constraint was last enabled or disabled

INDEX_OWNER

String

Name of the user owning the index

INDEX_NAME

String

Name of the index

PrimaryKeys

ColumnName DataType Description

OWNER

String

Owner of the constraint definition.

CONSTRAINT_NAME

String

Name of the constraint definition.

TABLE_NAME

String

Name associated with the table (or view) with constraint definition.

SEARCH_CONDITION

String

Text of search condition for a check constraint.

R_OWNER

String

Owner of table referred to in a referential constraint.

R_CONSTRAINT_NAME

String

Name of the unique constraint definition for referenced table.

DELETE_RULE

String

Delete rule for a referential constraint (CASCADE or NO ACTION).

STATUS

String

Enforcement status of constraint (ENABLED or DISABLED).

DEFERRABLE

String

Whether the constraint is deferrable.

VALIDATED

String

Whether all data obeys the constraint (VALIDATED or NOT VALIDATED).

GENERATED

String

Whether the name of the constraint is user or system generated.

BAD

String

A YES value indicates that this constraint specifies a century in an ambiguous manner. To avoid errors resulting from this ambiguity, rewrite the constraint using the TO_DATE function with a four-digit year.

RELY

String

Whether an enabled constraint is enforced or unenforced.

LAST_CHANGE

DateTime

When the constraint was last enabled or disabled.

INDEX_OWNER

String

Name of the user owning the index.

INDEX_NAME

String

Name of the index.

ForeignKeys

ColumnName DataType Description

PRIMARY_KEY_CONSTRAINT_NAME

String

Name of the constraint definition.

PRIMARY_KEY_OWNER

String

Owner of the constraint definition.

PRIMARY_KEY_TABLE_NAME

String

Name associated with the table (or view) with constraint definition

FOREIGN_KEY_OWNER

String

Owner of the constraint definition.

FOREIGN_KEY_CONSTRIANT_NAME

String

Name of the constraint definition.

FOREIGN_KEY_TABLE_NAME

String

Name associated with the table (or view) with constraint definition.

SEARCH_CONDITION

String

Text of search condition for a check constraint

R_OWNER

String

Owner of table referred to in a referential constraint.

R_CONSTRAINT_NAME

String

Name of the unique constraint definition for referenced table.

DELETE_RULE

String

Delete rule for a referential constraint (CASCADE or NO ACTION).

STATUS

String

Enforcement status of constraint (ENABLED or DISABLED).

VALIDATED

String

Whether all data obeys the constraint (VALIDATED or NOT VALIDATED).

GENERATED

String

Whether the name of the constraint is user or system generated.

RELY

String

Whether an enabled constraint is enforced or unenforced.

LAST_CHANGE

DateTime

When the constraint was last enabled or disabled.

INDEX_OWNER

String

Name of the user owning the index.

INDEX_NAME

String

Name of the index.

ForeignKeyColumns

ColumnName DataType Description

OWNER

String

Owner of the constraint definition.

CONSTRAINT_NAME

String

Name of the constraint definition.

TABLE_NAME

String

Name of the table with constraint definition.

COLUMN_NAME

String

Name of the column or attribute of the object type column specified in the constraint definition.

POSITION

Decimal

Original position of column or attribute in the definition of the object.

ProcedureParameters

ColumnName DataType Description

OWNER

String

Owner of the object.

OBJECT_NAME

String

Name of the procedure or function.

PACKAGE_NAME

String

Name of the procedure or function.

OBJECT_ID

Decimal

Object number of the object.

OVERLOAD

String

Overload unique identifier.

ARGUMENT_NAME

String

Name of the argument.

POSITION

Decimal

Position in the argument list, or null for a function return value.

SEQUENCE

Decimal

Argument sequence, including all nesting levels.

DATA_LEVEL

Decimal

Nesting depth of the argument for composite types.

DATA_TYPE

String

Data type of the argument.

DEFAULT_VALUE

String

Default value for the argument.

DEFAULT_LENGTH

Decimal

Length of the default value for the argument.

IN_OUT

String

Argument Direction (IN, OUT, or IN/OUT).

DATA_LENGTH

Decimal

Length of the column (in bytes).

DATA_PRECISION

Decimal

Length in decimal digits (NUMBER) or binary digits (FLOAT).

DATA_SCALE

Decimal

Digits to the right of the decimal point in a number.

RADIX

Decimal

Argument radix for a number.

CHARACTER_SET_NAME

String

Character set name for the argument.

TYPE_OWNER

String

Owner of the type of the argument.

TYPE_NAME

String

Name of the type of the argument. If the type is a package local type (that is, it is declared in a package specification), then this column displays the name of the package.

TYPE_SUBNAME

String

Relevant only for package local types. Displays the name of the type declared in the package identified in the TYPE_NAME column.

TYPE_LINK

String

Relevant only for package local types when the package identified in the TYPE_NAME column is a remote package. This column displays the database link used to refer to the remote package.

PLS_TYPE

String

For numeric arguments, the name of the PL/SQL type of the argument. Null otherwise.

CHAR_LENGTH

Decimal

Character limit for string data types.

CHAR_USED

String

Indicates whether the byte limit (B) or char limit (C) is official for the string

Microsoft SQL Server ODBC Driver

The Microsoft SQL Server ODBC Driver supports the following specific schema collections in addition to the common schema collections:

  • Tables

  • Indexes

  • Columns

  • Procedures

  • ProcedureColumns

  • ProcedureParameters

  • Views

Tables and Views

ColumnName DataType

TABLE_CAT

String

TABLE_SCHEM

String

TABLE_NAME

String

TABLE_TYPE

String

REMARKS

String

Indexes

ColumnName DataType

TABLE_CAT

String

TABLE_SCHEM

String

TABLE_NAME

String

NON_UNIQUE

Int16

INDEX_QUALIFIER

String

INDEX_NAME

String

TYPE

Int16

ORDINAL_POSITION

Int16

COLUMN_NAME

String

ASC_OR_DESC

String

CARDINATLITY

Int32

PAGES

Int32

FILTER_CONDITION

String

SS_TYPE_SCHEMA

String

SS_DATA_TYPE

Byte

Columns

ColumnName DataType

TABLE_CAT

String

TABLE_SCHEM

String

TABLE_NAME

String

COLUMN_NAME

String

DATA_TYPE

Int16

TYPE_NAME

String

COLUMN_SIZE

Int32

BUFFER_LENGTH

Int32

DECIMAL_DIGITS

Int16

NUM_PREC_RADIX

Int16

NULLABLE

Int16

REMARKS

String

COLUMN_DEF

String

SQL_DATA_TYPE

Int16

SQL_DATETIME_SUB

Int16

CHAR_OCTET_LENGTH

Int32

ORDINAL_POSITION

Int32

IS_NULLABLE

String

SS_TYPE_CATALOG

String

SS_TYPE_SCHEMA

String

SS_DATA_TYPE

Byte

Procedures

ColumnName DataType

PROCEDURE_CAT

String

PROCEDURE_SCHEM

String

PROCEDURE_NAME

String

NUM_INPUT_PARAMS

Int32

NUM_OUTPUT_PARAMS

Int32

NUM_RESULT_SETS

Int32

REMARKS

String

PROCEDURE_TYPE

Int16

ProcedureColumns

ColumnName DataType

PROCEDURE_CAT

String

PROCEDURE_SCHEM

String

PROCEDURE_NAME

String

COLUMN_NAME

String

COLUMN_TYPE

Int16

DATA_TYPE

Int16

TYPE_NAME

String

COLUMN_SIZE

Int32

BUFFER_LENGTH

Int32

DECIMAL_DIGITS

Int16

NUM_PREC_RADIX

Int16

NULLABLE

Int16

REMARKS

String

COLUMN_DEF

String

SQL_DATA_TYPE

Int16

SQL_DATETIME_SUB

Int16

CHAR_OCTET_LENGTH

Int32

ORDINAL_POSITION

Int32

IS_NULLABLE

String

SS_TYPE_CATALOG

String

SS_TYPE_SCHEMA

String

SS_DATA_TYPE

Byte

ProcedureParameters

ColumnName DataType

PROCEDURE_CAT

String

PROCEDURE_SCHEM

String

PROCEDURE_NAME

String

COLUMN_NAME

String

COLUMN_TYPE

Int16

DATA_TYPE

Int16

TYPE_NAME

String

COLUMN_SIZE

Int32

BUFFER_LENGTH

Int32

DECIMAL_DIGITS

Int16

NUM_PREC_RADIX

Int16

NULLABLE

Int16

REMARKS

String

COLUMN_DEF

String

SQL_DATA_TYPE

Int16

SQL_DATETIME_SUB

Int16

CHAR_OCTET_LENGTH

Int32

ORDINAL_POSITION

Int32

IS_NULLABLE

String

SS_TYPE_CATALOG

String

SS_TYPE_SCHEMA

String

SS_DATA_TYPE

Byte

Microsoft Oracle ODBC Driver

The Microsoft SQL Server Oracle ODBC Driver supports the following specific schema collections in addition to the common schema collections:

  • Tables

  • Columns

  • Procedures

  • ProcedureColumns

  • ProcedureParameters

  • Views

  • Indexes

Tables and Views

ColumnName DataType

TABLE_QUALIFIER

String

TABLE_OWNER

String

TABLE_NAME

String

TABLE_TYPE

String

REMARKS

String

Columns

ColumnName DataType

TABLE_QUALIFIER

String

TABLE_OWNER

String

TABLE_NAME

String

COLUMN_NAME

String

DATA_TYPE

Int16

TYPE_NAME

String

PRECISION

Int32

LENGTH

Int32

SCALE

Int16

RADIX

Int16

NULLABLE

Int16

REMARKS

String

ORDINAL_POSITION

Int32

Procedures

ColumnName DataType

PROCEDURE_QUALIFIER

String

PROCEDURE_OWNER

String

PROCEDURE_NAME

String

NUM_INPUT_PARAMS

Int16

NUM_OUTPUT_PARAMS

Int16

NUM_RESULT_SETS

Int16

REMARKS

String

PROCEDURE_TYPE

Int16

ProcedureColumns

ColumnName DataType

PROCEDURE_QUALIFIER

String

PROCEDURE_OWNER

String

PROCEDURE_NAME

String

COLUMN_NAME

String

COLUMN_TYPE

Int16

DATA_TYPE

Int16

TYPE_NAME

String

PRECISION

Int32

LENGTH

Int32

SCALE

Int16

RADIX

Int16

NULLABLE

Int16

REMARKS

String

OVERLOAD

Int32

ORDINAL_POSITION

Int32

Microsoft Jet ODBC Driver

The Microsoft SQL Server Jet ODBC Driver supports the following specific schema collections in addition to the common schema collections:

  • Tables

  • Indexes

  • Columns

  • Procedures

  • ProcedureColumns

  • ProcedureParameters

  • Views

Tables and Views

ColumnName DataType

TABLE_QUALIFIER

String

TABLE_OWNER

String

TABLE_NAME

String

TABLE_TYPE

String

REMARKS

String

Columns

ColumnName DataType

TABLE_QUALIFIER

String

TABLE_OWNER

String

TABLE_NAME

String

COLUMN_NAME

String

DATA_TYPE

Int16

TYPE_NAME

String

PRECISION

Int32

LENGTH

Int32

SCALE

Int16

RADIX

Int16

NULLABLE

Int16

REMARKS

String

ORDINAL_POSITION

Int32

Procedures

ColumnName DataType

PROCEDURE_QUALIFIER

String

PROCEDURE_OWNER

String

PROCEDURE_NAME

String

NUM_INPUT_PARAMS

Int16

NUM_OUTPUT_PARAMS

Int16

NUM_RESULT_SETS

Int16

REMARKS

String

PROCEDURE_TYPE

Int16

ProcedureColumns

ColumnName DataType

PROCEDURE_QUALIFIER

String

PROCEDURE_OWNER

String

PROCEDURE_NAME

String

COLUMN_NAME

String

COLUMN_TYPE

Int16

DATA_TYPE

Int16

TYPE_NAME

String

PRECISION

Int32

LENGTH

Int32

SCALE

Int16

RADIX

Int16

NULLABLE

Int16

REMARKS

String

OVERLOAD

Int32

ORDINAL_POSITION

Int32

ProcedureParameters

ColumnName DataType

PROCEDURE_CAT

String

PROCEDURE_SCHEM

String

PROCEDURE_NAME

String

COLUMN_NAME

String

COLUMN_TYPE

Int16

DATA_TYPE

Int16

TYPE_NAME

String

COLUMN_SIZE

Int32

BUFFER_LENGTH

Int32

DECIMAL_DIGITS

Int16

NUM_PREC_RADIX

Int16

NULLABLE

Int16

REMARKS

String

COLUMN_DEF

String

SQL_DATA_TYPE

Int16

SQL_DATETIME_SUB

Int16

CHAR_OCTET_LENGTH

Int32

ORDINAL_POSITION

Int32

IS_NULLABLE

String

Microsoft SQL Server OLEDB Provider

The Microsoft SQL Server OLEDB Driver supports the following specific schema collections in addition to the common schema collections:

  • Tables

  • Columns

  • Procedures

  • ProcedureParameters

  • Catalog

  • Indexes

Tables

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

TABLE_TYPE

String

TABLE_GUID

Guid

DESCRIPTION

String

TABLE_PROPID

Int64

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

Columns

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

COLUMN_NAME

String

COLUMN_GUID

Guid

COLUMN_PROPID

Int64

ORDINAL_POSITION

Int64

COLUMN_HASDEFAULT

Boolean

COLUMN_DEFAULT

String

COLUMN_FLAGS

Int64

IS_NULLABLE

Boolean

DATA_TYPE

Int32

TYPE_GUID

Guid

CHARACTER_MAXIMUM_LENGTH

Int64

CHARACTER_OCTET_LENGTH

Int64

NUMERIC_PRECISION

Int32

NUMERIC_SCALE

Int16

DATETIME_PRECISION

Int64

CHARACTER_SET_CATALOG

String

CHARACTER_SET_SCHEMA

String

CHARACTER_SET_NAME

String

COLLATION_CATALOG

String

COLLATION_SCHEMA

String

COLLATION_NAME

String

DOMAIN_CATALOG

String

DOMAIN_SCHEMA

String

DOMAIN_NAME

String

DESCRIPTION

String

COLUMN_LCID

Int32

COLUMN_COMPFLAGS

Int32

COLUMN_SORTID

Int32

COLUMN_TDSCOLLATION

Byte[]

IS_COMPUTED

Boolean

Procedures

ColumnName DataType

PROCEDURE_CATALOG

String

PROCEDURE_SCHEMA

String

PROCEDURE_NAME

String

PROCEDURE_TYPE

Int16

PROCEDURE_DEFINITION

String

DESCRIPTION

String

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

ProcedureParameters

ColumnName DataType

PROCEDURE_CATALOG

String

PROCEDURE_SCHEMA

String

PROCEDURE_NAME

String

PARAMETER_NAME

String

ORDINAL_POSITION

Int32

PARAMETER_TYPE

Int32

PARAMETER_HASDEFAULT

Boolean

PARAMETER_DEFAULT

String

IS_NULLABLE

Boolean

DATA_TYPE

Int32

CHARACTER_MAXIMUM_LENGTH

Int64

CHARACTER_OCTET_LENGTH

Int64

NUMERIC_PRECISION

Int32

NUMERIC_SCALE

Int16

DESCRIPTION

String

TYPE_NAME

String

LOCAL_TYPE_NAME

String

Catalog

ColumnName DataType

CATALOG_NAME

String

DESCRIPTION

String

Indexes

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

INDEX_CATALOG

String

INDEX_SCHEMA

String

INDEX_NAME

String

PRIMARY_KEY

Boolean

UNIQUE

Boolean

CLUSTERED

Boolean

TYPE

Int32

FILL_FACTOR

Int32

INITIAL_SIZE

Int32

NULLS

Int32

SORT_BOOKMARKS

Boolean

AUTO_UPDATE

Boolean

NULL_COLLATION

Int32

ORDINAL_POSITION

Int64

COLUMN_NAME

String

COLUMN_GUID

Guid

COLUMN_PROPID

Int64

COLLATION

Int16

CARDINALITY

Decimal

PAGES

Int32

FILTER_CONDITION

String

INTEGRATED

Boolean

Microsoft Oracle OLEDB Provider

The Microsoft Oracle OLEDB Driver supports the following specific schema collections in addition to the common schema collections:

  • Tables

  • Columns

  • Procedures

  • ProcedureColumns

  • ProcedureParameters

  • Views

  • Indexes

Tables

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

TABLE_TYPE

String

TABLE_GUID

Guid

DESCRIPTION

String

TABLE_PROPID

Int64

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

Columns

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

COLUMN_NAME

String

COLUMN_GUID

Guid

COLUMN_PROPID

Int64

ORDINAL_POSITION

Int64

COLUMN_HASDEFAULT

Boolean

COLUMN_DEFAULT

String

COLUMN_FLAGS

Int64

IS_NULLABLE

Boolean

DATA_TYPE

Int32

TYPE_GUID

Guid

CHARACTER_MAXIMUM_LENGTH

Int64

CHARACTER_OCTET_LENGTH

Int64

NUMERIC_PRECISION

Int32

NUMERIC_SCALE

Int16

DATETIME_PRECISION

Int64

CHARACTER_SET_CATALOG

String

CHARACTER_SET_SCHEMA

String

CHARACTER_SET_NAME

String

COLLATION_CATALOG

String

COLLATION_SCHEMA

String

COLLATION_NAME

String

DOMAIN_CATALOG

String

DOMAIN_SCHEMA

String

DOMAIN_NAME

String

DESCRIPTION

String

Procedures

ColumnName DataType

PROCEDURE_CATALOG

String

PROCEDURE_SCHEMA

String

PROCEDURE_NAME

String

PROCEDURE_TYPE

Int16

PROCEDURE_DEFINITION

String

DESCRIPTION

String

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

ProcedureColumns

ColumnName DataType

PROCEDURE_CATALOG

String

PROCEDURE_SCHEMA

String

PROCEDURE_NAME

String

COLUMN_NAME

String

COLUMN_GUID

Guid

COLUMN_PROPID

Int64

ROWSET_NUMBER

Int64

ORDINAL_POSITION

Int64

IS_NULLABLE

Boolean

DATA_TYPE

Int32

TYPE_GUID

Guid

CHARACTER_MAXIMUM_LENGTH

Int64

CHARACTER_OCTET_LENGTH

Int64

NUMERIC_PRECISION

Int32

NUMERIC_SCALE

Int16

DESCRIPTION

String

OVERLOAD

Int16

Views

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

VIEW_DEFINITION

String

CHECK_OPTION

Boolean

IS_UPDATABLE

Boolean

DESCRIPTION

String

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

Indexes

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

INDEX_CATALOG

String

INDEX_SCHEMA

String

INDEX_NAME

String

PRIMARY_KEY

Boolean

UNIQUE

Boolean

CLUSTERED

Boolean

TYPE

Int32

FILL_FACTOR

Int32

INITIAL_SIZE

Int32

NULLS

Int32

SORT_BOOKMARKS

Boolean

AUTO_UPDATE

Boolean

NULL_COLLATION

Int32

ORDINAL_POSITION

Int64

COLUMN_NAME

String

COLUMN_GUID

Guid

COLUMN_PROPID

Int64

COLLATION

Int16

CARDINALITY

Decimal

PAGES

Int32

FILTER_CONDITION

String

INTEGRATED

Boolean

Microsoft Jet OLEDB Provider

The Microsoft Jet OLEDB Driver supports the following specific schema collections in addition to the common schema collections:

  • Tables

  • Columns

  • Procedures

  • Views

  • Indexes

Tables

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

TABLE_TYPE

String

TABLE_GUID

Guid

DESCRIPTION

String

TABLE_PROPID

Int64

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

Columns

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

COLUMN_NAME

String

COLUMN_GUID

Guid

COLUMN_PROPID

Int64

ORDINAL_POSITION

Int64

COLUMN_HASDEFAULT

Boolean

COLUMN_DEFAULT

String

COLUMN_FLAGS

Int64

IS_NULLABLE

Boolean

DATA_TYPE

Int32

TYPE_GUID

Guid

CHARACTER_MAXIMUM_LENGTH

Int64

CHARACTER_OCTET_LENGTH

Int64

NUMERIC_PRECISION

Int32

NUMERIC_SCALE

Int16

DATETIME_PRECISION

Int64

CHARACTER_SET_CATALOG

String

CHARACTER_SET_SCHEMA

String

CHARACTER_SET_NAME

String

COLLATION_CATALOG

String

COLLATION_SCHEMA

String

COLLATION_NAME

String

DOMAIN_CATALOG

String

DOMAIN_SCHEMA

String

DOMAIN_NAME

String

DESCRIPTION

String

Procedures

ColumnName DataType

PROCEDURE_CATALOG

String

PROCEDURE_SCHEMA

String

PROCEDURE_NAME

String

PROCEDURE_TYPE

Int16

PROCEDURE_DEFINITION

String

DESCRIPTION

String

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

Views

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

VIEW_DEFINITION

String

CHECK_OPTION

Boolean

IS_UPDATABLE

Boolean

DESCRIPTION

String

DATE_CREATED

DateTime

DATE_MODIFIED

DateTime

Indexes

ColumnName DataType

TABLE_CATALOG

String

TABLE_SCHEMA

String

TABLE_NAME

String

INDEX_CATALOG

String

INDEX_SCHEMA

String

INDEX_NAME

String

PRIMARY_KEY

Boolean

UNIQUE

Boolean

CLUSTERED

Boolean

TYPE

Int32

FILL_FACTOR

Int32

INITIAL_SIZE

Int32

NULLS

Int32

SORT_BOOKMARKS

Boolean

AUTO_UPDATE

Boolean

NULL_COLLATION

Int32

ORDINAL_POSITION

Int64

COLUMN_NAME

String

COLUMN_GUID

Guid

COLUMN_PROPID

Int64

COLLATION

Int16

CARDINALITY

Decimal

PAGES

Int32

FILTER_CONDITION

String

INTEGRATED

Boolean

See Also

Other Resources

Obtaining Schema Information from a Database