Developer Reference > Data Access Methods > Data Provider for .NET > B. Getting Schema Information
B. Getting Schema Information
Applications can request that data providers find and return metadata for a database. Schema collections specific to each data provider expose database schema elements such as tables and columns. The data provider uses the GetSchema method of the Connection class. You can also retrieve schema information from a result set, as described in Columns Returned by the GetSchemaTable Method.
The data provider also includes provider-specific schema collections. Using the schema collection name MetaDataCollections, you can return a list of the supported schema collections, and the number of restrictions that they support.
Columns Returned by the GetSchemaTable Method
While a PsqlDataReader is open, you can retrieve schema information from the result set. The result set produced for PsqlDataReader.GetSchemaTable() returns the columns described in the following table, in the order shown.
Column
Description
ColumnName
Specifies the name of the column, which might not be unique. If the name cannot be determined, a null value is returned. This name reflects the most recent renaming of the column in the current view or command text.
ColumnOrdinal
Specifies the ordinal of the column, which cannot be null. The bookmark column of the row, if any, is 0. Other columns are numbered starting with 1.
ColumnSize
Specifies the maximum possible length of a value in the column. For columns that use a fixed-length data type, this is the size of the data type.
NumericPrecision
Specifies the precision of the column, which depends on how the column is defined in ProviderType.
If ProviderType is a numeric data type, this is the maximum precision of the column.
If ProviderType is not a numeric data type, the value is null.
NumericScale
Specifies the number of digits to the right of the decimal point if ProviderType is DBTYPE_DECIMAL or DBTYPE_NUMERIC. Otherwise, this is a null value.
The value depends on how the column is defined in ProviderType.
DataType
Maps to the .NET Framework type of the column.
ProviderType
Specifies the indicator of the column data type. This column cannot contain a null value.
If the data type of the column varies from row to row, this must be Object.
IsLong
Set if the column contains a BLOB that contains very long data. The setting of this flag corresponds to the value of the IS_LONG column in the PROVIDER_TYPES rowset for the data type.
The definition of very long data is provider-specific.
AllowDBnull
Set if the consumer can set the column to a null value, or if the data provider cannot determine whether the consumer can set the column to a null value. Otherwise, no value is set.
A column can contain null values, even if it cannot be set to a null value.
IsReadOnly
Determines whether a column can be changed.
The value is true if the column can be modified; otherwise, the value is false.
IsRowVersion
Is set if the column contains a persistent row identifier that cannot be written to, and has no meaningful value except to identify the row.
IsUnique
Specifies whether the column constitutes a key by itself or if there is a constraint of type UNIQUE that applies only to this column.
If set to true, no two rows in the base table (the table returned in BaseTableName) can have the same value in this column.
If set to false (the initial default), the column can contain duplicate values in the base table.
IsKey
Specifies whether a set of columns uniquely identifies a row in the rowset. This set of columns may be generated from a base table primary key, a unique constraint, or a unique index.
The value is true if the column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The value is false if the column is not required to uniquely identify the row.
IsAutoIncrement
Specifies whether the column assigns values to new rows in fixed increments.
If set to VARIANT_TRUE, the column assigns values to new rows in fixed increments.
If set to VARIANT_FALSE (the initial default), the column does not assign values to new rows in fixed increments.
BaseSchemaName
Specifies the name of the schema in the database that contains the column. The value is null if the base schema name cannot be determined.
The initial default is null.
BaseCatalogName
Specifies the name of the catalog in the data store that contains the column. A null value is used if the base catalog name cannot be determined.
The initial default is null.
BaseTableName
Specifies the name of the table or view in the data store that contains the column. A null value is used if the base table name cannot be determined.
The initial default is null.
BaseColumnName
Specifies the name of the column in the data store. This might be different than the column name returned in the ColumnName column if an alias was used. A null value is used if the base column name cannot be determined or if the rowset column is derived from, but is not identical to, a column in the database.
The initial default is null.
IsAliased
Specifies whether the name of the column is an alias. The value true is returned if the column name is an alias; otherwise, false is returned.
IsExpression
Specifies whether the name of the column is an expression. The value true is returned if the column is an expression; otherwise, false is returned.
IsIdentity
Specifies whether the name of the column is an identity column. The value true is returned if the column is an identity column; otherwise, false is returned.
IsHidden
Specifies whether the name of the column is hidden. The value true is returned if the column is hidden; otherwise, false is returned.
Retrieving Schema Metadata with the GetSchema Method
Applications use the GetSchema method of the Connection object to retrieve Schema Metadata about a data provider and/or data source. Each provider implements a number of Schema collections, including the five standard metadata collections.
Additional collections are specified and must be supported to return Schema information from the data provider.
See Additional Schema Collections for details about the other collections supported by the data providers.
Note:  Refer to the .NET Framework documentation for additional background functional requirements, including the required data type for each ColumnName.
MetaDataCollections Schema Collections
The MetaDataCollections schema collection is a list of the schema collections available to the logged in user. The MetaDataCollection can return the supported columns described in the following table, in any order.
ColumnName
Description
CollectionName
The name of the collection to pass to the GetSchema method to return the collection.
NumberOfRestrictions
The number of restrictions that may be specified for the collection.
NumberOfIdentifierParts
The number of parts in the composite identifier/data base object name.
DataSourceInformation Schema Collection
The DataSourceInformation schema collection can return the supported columns, described in the following table, in any order. Note that only one row is returned.
ColumnName
Description
CompositeIdentifierSeparatorPattern
The regular expression to match the composite separators in a composite identifier.
DataSourceProductName
The name of the product accessed by the data provider.
DataSourceProductVersion
Indicates the version of the product accessed by the data provider, in the data source’s native format.
DataSourceProductVersionNormalized
A normalized version for the data source. This allows the version to be compared with String.Compare().
GroupByBehavior
Specifies the relationship between the columns in a GROUP BY clause and the non-aggregated columns in the select list.
Host
The host to which the data provider is connected.
IdentifierCase
Indicates whether non-quoted identifiers are treated as case sensitive.
IdentifierPattern
A regular expression that matches an identifier and has a match value of the identifier.
OrderByColumnsInSelect
Specifies whether columns in an ORDER BY clause must be in the select list. A value of true indicates that they are required to be in the Select list, a value of false indicates that they are not required to be in the Select list.
ParameterMarkerFormat
A format string that represents how to format a parameter.
ParameterMarkerPattern
A regular expression that matches a parameter marker. It will have a match value of the parameter name, if any.
ParameterNameMaxLength
The maximum length of a parameter name in characters.
ParameterNamePattern
A regular expression that matches the valid parameter names.
QuotedIdentifierCase
Indicates whether quoted identifiers are treated as case sensitive.
QuotedIdentifierPattern
A regular expression that matches a quoted identifier and has a match value of the identifier itself without the quotation marks.
StatementSeparatorPattern
A regular expression that matches the statement separator.
StringLiteralPattern
A regular expression that matches a string literal and has a match value of the literal itself.
SupportedJoinOperators
Specifies the types of SQL join statements that are supported by the data source.
DataTypes Collection
The following table describes the supported columns of the DataTypes schema collection. The columns can be returned in any order.
ColumnName
Description
ColumnSize
The length of a non-numeric column or parameter refers to either the maximum or the length defined for this type by the data provider.
CreateFormat
Format string that represents how to add this column to a data definition statement, such as CREATE TABLE.
CreateParameters
The creation parameters that must be specified when creating a column of this data type. Each creation parameter is listed in the string, separated by a comma in the order they are to be supplied.
For example, the SQL data type DECIMAL needs a precision and a scale. In this case, the creation parameters should contain the string "precision, scale".
In a text command to create a DECIMAL column with a precision of 10 and a scale of 2, the value of the CreateFormat column might be DECIMAL({0},{1})" and the complete type specification would be DECIMAL(10,2).
DataType
The name of the .NET Framework type of the data type.
IsAutoIncrementable
Specifies whether values of a data type are auto-incremented.
true: Values of this data type may be auto-incremented.
false: Values of this data type may not be auto-incremented.
IsBestMatch
Specifies whether the data type is the best match between all data types in the data store and the .NET Framework data type indicated by the value in the DataType column.
true: The data type is the best match.
false: The data type is not the best match.
IsCaseSensitive
Specifies whether the data type is both a character type and case-sensitive.
true: The data type is a character type and is case-sensitive.
false: The data type is not a character type or is not case-sensitive.
IsConcurrencyType
true: The data type is updated by the database every time the row is changed and the value of the column is different from all previous values.
false: The data type is not updated by the database every time the row is changed.
IsFixedLength
true: Columns of this data type created by the data definition language (DDL) will be of fixed length.
false: Columns of this data type created by the DDL will be of variable length.
IsFixedPrecisionScale
true: The data type has a fixed precision and scale.
false: The data type does not have a fixed precision and scale.
IsLiteralsSupported
true: The data type can be expressed as a literal.
false: The data type cannot be expressed as a literal.
IsLong
true: The data type contains very long data. The definition of very long data is provider-specific.
false: The data type does not contain very long data.
IsNullable
true: The data type is nullable.
false: The data type is not nullable.
IsSearchable
true: The data type contains very long data. The definition of very long data is provider-specific.
false: The data type does not contain very long data.
IsSearchableWithLike
true: The data type can be used with the LIKE predicate.
false: The data type cannot be used with the LIKE predicate.
IsUnisgned
true: The data type is unsigned.
false: The data type is signed.
LiteralPrefix
The prefix applied to a given literal.
LiteralSuffix
The suffix applied to a given literal.
MaximumScale
If the type indicator is a numeric type, this is the maximum number of digits allowed to the right of the decimal point.
Otherwise, this is DBNull.Value.
MinimumScale
If the type indicator is a numeric type, this is the minimum number of digits allowed to the right of the decimal point.
Otherwise, this is DBNull.Value.
ProviderDbType
The provider-specific type value that should be used when specifying a parameter's type.
TypeName
The provider-specific data type name.
ReservedWords Collection
This schema collection exposes information about the words that are reserved by the database to which the data provider is connected. The following table describes the columns that the data provider supports.
ColumnName
Description
Reserved Word
Provider-specific reserved words.
Restrictions Collection
The Restrictions schema collection exposes information about the restrictions supported by the data provider that is currently connected to the database. The following table describes the columns returned by the data providers. The columns can be returned in any order.
The ADO.NET Data Provider uses standardized names for restrictions. If the data provider supports a restriction for a Schema method, it always uses the same name for the restriction.
The case sensitivity of any restriction value is determined by the underlying database, and can be determined by the IdentifierCase and QuotedIdentifierCase values in the DataSourceInformation collection (see DataSourceInformation Schema Collection).
ColumnName
Description
CollectionName
The name of the collection to which the specified restrictions apply.
RestrictionName
The name of the restriction in the collection.
RestrictionDefault
Ignored.
RestrictionNumber
The actual location in the collection restrictions for this restriction.
IsRequired
Specifies whether the restriction is required.
See Additional Schema Collections for the restrictions that apply to the each of the additional supported schema collections.
Additional Schema Collections
The Zen ADO.NET Data Provider supports the following additional schema collections:
Columns Schema Collection
Description: The Columns schema collection identifies the columns of tables (including views) defined in the catalog that are accessible to a given user. The following table identifies the columns of tables defined in the catalog that are accessible to a given user.
Number of restrictions: 3
Restrictions available: TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
Sort order: TABLE_CATALOG, TABLE_NAME, ORDINAL_POSITION
 
Column Name
.NET Framework DataType1
Description
CHARACTER_MAXIMUM_ LENGTH
Int32
The maximum possible length of a value in the column. For character, binary, or bit columns, this is one of the following:
• The maximum length of the column in characters, bytes, or bits, respectively, if one is defined.
• The maximum length of the data type in characters, bytes, or bits, respectively, if the column does not have a defined length.
• Zero (0) if neither the column or the data type has a defined maximum length, or if the column is not a character, binary, or bit column.
CHARACTER_OCTET_LENGTH
Int32
The maximum length in octets (bytes) of the column, if the type of the column is character or binary.
A value of zero (0) means the column has no maximum length or that the column is not a character or binary column.
COLUMN_DEFAULT
String
The default value of the column.
COLUMN_HASDEFAULT
Boolean
TRUE: The column has a default value.
FALSE: The column does not have a default value, or it is unknown whether the column has a default value.
COLUMN_NAME
String
The name of the column; this might not be unique.
DATA_TYPE
Object
The indicator of the column data type.
This value cannot be null.
IS_NULLABLE
Boolean
TRUE: The column might be nullable.
FALSE: The column is known not to be nullable.
NATIVE_DATA_TYPE
String
The data source description of the type.
This value cannot be null.
NUMERIC_PRECISION
Int32
If the column data type is of numeric data, this is the maximum precision of the column.
NUMERIC_PRECISION_RADIX
Int32
The radix indicates in which base the values in NUMERIC_PRECISION and NUMERIC_SCALE are expressed. It is only useful to return either 2 or 10.
NUMERIC_SCALE
Int16
If the column type is a numeric type that has a scale, this is the number of digits to the right of the decimal point.
ORDINAL_POSITION
Int32
The ordinal of the column. Columns are numbered starting from one.
PROVIDER_DEFINED_TYPE
Int32
The data source defined type of the column is mapped to the type enumeration of the data provider, for example, the PsqlDbType enumeration.
This value cannot be null.
PROVIDER_GENERIC_TYPE
Int32
The provider-defined type of the column is mapped to the System.Data.DbType enumeration.
This value cannot be null.
TABLE_CATALOG
String
The database name.
TABLE_NAME
String
The table name.
TABLE_OWNER
String
The table owner.
1 All classes are System.XXX. For example, System.String.

ForeignKeys Schema Collection
Description: The ForeignKeys schema collection identifies the foreign key columns defined in the catalog by a given user.
Number of restrictions: 2
Restrictions available: FK_TABLE_CATALOG, PK_TABLE_NAME
Sort order: FK_TABLE_CATALOG, FK_TABLE_NAME
 
Column Name
.NET Framework Datatype1
Description
DEFERRABILITY
String
The deferrability of the foreign key. The value is one of the following:
• INITIALLY DEFERRED
• INITIALLY IMMEDIATE
• NOT DEFERRABLE
DELETE_RULE
String
If a delete rule was specified, the value is one of the following:
CASCADE: A referential action of CASCADE was specified.
SET NULL: A referential action of SET NULL was specified.
SET DEFAULT: A referential action of SET DEFAULT was specified.
NO ACTION: A referential action of NO ACTION was specified.
FK_COLUMN_NAME
String
The foreign key column name.
FK_NAME
String
The foreign key name. This is a required restriction.
FK_TABLE_CATALOG
String
The catalog name in which the foreign key table is defined.
FK_TABLE_NAME
String
The foreign key table name. This is a required restriction.
FK_TABLE_OWNER
String
The foreign key table owner. This is a required restriction.
ORDINAL
Int32
The order of the column names in the key. For example, a table might contain several foreign key references to another table. The ordinal starts over for each reference; for example, two references to a three-column key would return 1, 2, 3, 1, 2, 3.
PK_COLUMN_NAME
String
The primary key column name.
PK_NAME
String
The primary key name.
PK_TABLE_CATALOG
String
The catalog name in which the primary key table is defined.
PK_TABLE_NAME
String
The primary key table name.
PK_TABLE_OWNER
String
The primary key table owner. This is a required restriction.
UPDATE_RULE
String
If an update rule was specified, one of the following:
CASCADE: A referential action of CASCADE was specified.
SET NULL: A referential action of SET NULL was specified.
SET DEFAULT: A referential action of SET DEFAULT was specified.
NO ACTION: A referential action of NO ACTION was specified.
1 All classes are System.XXX. For example, System.String

Indexes Schema Collection
Description: The Indexes schema collection identifies the indexes defined in the catalog that are owned by a given user.
Number of restrictions: 2
Restrictions available: TABLE_CATALOG, TABLE_NAME
Sort order: UNIQUE, TYPE, INDEX_CATALOG, INDEX_NAME, ORDINAL_POSITION
 
Column Name
.NET Framework DataType1
Description
CARDINALITY
Int32
The number of unique values in the index.
COLLATION
String
This is one of the following:
ASC: The sort sequence for the column is ascending.
DESC: The sort sequence for the column is descending.
COLUMN_NAME
String
The column name.
FILTER_CONDITION
String
The WHERE clause that identifies the filtering restriction.
INDEX_CATALOG
String
The catalog name.
INDEX_NAME
String
The index name.
ORDINAL_POSITION
Int32
The ordinal position of the column in the index, starting with 1.
PAGES
Int32
The number of pages used to store the index.
TABLE_CATALOG
String
The catalog name.
TABLE_NAME
String
The table name.
TABLE_OWNER
String
The table owner.
TABLE_QUALIFIER
String
The table qualifier.
TYPE
String
The type of the index. One of the following values:
• BTREE: The index is a B+-tree.
• HASH: The index is a hash file using, for example, linear or extensible hashing.
• CONTENT: The index is a content index.
• OTHER: The index is some other type of index.
UNIQUE
Boolean
 
1 All classes are System.XXX. For example, System.String.

PrimaryKeys Schema Collection
Description: The PrimaryKeys schema collection identifies the primary key columns defined in the catalog by a given user.
Number of restrictions: 2
Restrictions available: TABLE_CATALOG, TABLE_NAME
Sort order: TABLE_CATALOG, TABLE_NAME
 
Column Name
.NET Framework DataType1
Description
COLUMN_NAME
String
The primary key column name.
ORDINAL
Int32
The order of the column names in the key.
PK_NAME
String
The primary key name.
TABLE_CATALOG
String
The database name in which the table is defined.
TABLE_NAME
String
The table name.
TABLE_OWNER
String
The table owner.
1 All classes are System.XXX. For example, System.String.

ProcedureParameters Schema Collection
Description: The ProcedureParameters schema collection returns information about the parameters and return codes of procedures that are part of the Procedures collection.
Number of restrictions: 3
Restrictions available: PROCEDURE_CATALOG, PROCEDURE_NAME, PARAMETER_NAME
Sort order: PROCEDURE_CATALOG, PROCEDURE_NAME, ORDINAL_POSITION
 
Column Name
.NET Framework DataType1
Description
CHARACTER_MAXIMUM_LENGTH
Int32
The maximum length of the parameter.
CHARACTER_OCTET_LENGTH
Int32
The maximum length in octets (bytes) of the parameter, if the type of the parameter is character or binary.
If the parameter has no maximum length, the value is zero (0).
For all other types of parameters, the value is -1.
DATA_TYPE
Object
The indicator of the column data type.
This value cannot be null.
DESCRIPTION
String
The description of the parameter. For example, the description of the Name parameter in a procedure that adds a new employee might be Employee name.
IS_NULLABLE
Boolean
TRUE: The parameter might be nullable.
FALSE: The parameter is not nullable.
NATIVE_DATA_TYPE
String
The data source description of the type.
This value cannot be null.
NULLABLE
String
Denotes whether null value can be specified for the parameter. YES and NO are the two possible values.
NUMERIC_PRECISION
Int32
If the column data type is numeric, this is the maximum precision of the column.
If the column data type is not numeric, this is DbNull.
NUMERIC_PRECISION_RADIX
Int32
Applicable when the column data type is numeric.
The radix indicates in which base the values in NUMERIC_PRECISION and NUMERIC_SCALE are expressed. It is only useful to return either 2 or 10.
NUMERIC_SCALE
Int16
If the column data type is a numeric type that has a scale, this is the number of digits to the right of the decimal point.
Otherwise, this is DbNull.
ORDINAL_POSITION
Int32
If the parameter is an input, input/output, or output parameter, this is the one-based ordinal position of the parameter in the procedure call.
If the parameter is the return value, this is DbNull.
PARAMETER_DEFAULT
String
The default value of parameter.
If the default value is a NULL, then the PARAMETER_HASDEFAULT column returns TRUE and the PARAMETER_DEFAULT column will not exist.
If PARAMETER_HASDEFAULT is set to FALSE, then the PARAMETER_DEFAULT column will not exist.
PARAMETER_HASDEFAULT
Boolean
TRUE: The parameter has a default value.
FALSE: The parameter does not have a default value, or it is unknown whether the parameter has a default value.
PARAMETER_NAME
String
The parameter name. If the parameter is not named, this is DbNull.
PARAMETER_TYPE
String
This is one of the following:
INPUT: The parameter is an input parameter.
INPUTOUTPUT: The parameter is an input/output parameter.
OUTPUT: The parameter is an output parameter.
RETURNVALUE: The parameter is a procedure return value.
UNKNOWN: The parameter type is unknown to the data provider.
PROCEDURE_CATALOG
String
The catalog name.
PROCEDURE_NAME
String
The procedure name.
PROCEDURE_COLUMN_NAME
String
The procedure column name.
PROVIDER_DEFINED_TYPE
Int32
The data source defined type of the column as mapped to the type enumeration of the data provider, for example, the PSQLDbType enumeration.
This value cannot be null.
PROVIDER_GENERIC_TYPE
Int32
The data source defined type of the column as mapped to the System.Data.DbType enumeration.
This value cannot be null.
SQL_DATETIME_SUB
Object
Applicable when the column data type is DateTime.
1 All classes are System.XXX. For example, System.String.

Procedures Schema Collection
Description: The Procedures schema collection identifies the procedures defined in the catalog. When possible, only procedures for which the connected user has execute permission should be returned.
Number of restrictions: 2
Restrictions available: PROCEDURE_CATALOG, PROCEDURE_NAME, PROCEDURE_TYPE
Sort order: PROCEDURE_CATALOG, PROCEDURE_NAME
 
Column Name
.NET Framework DataType1
Description
PROCEDURE_CATALOG
String
The database name.
PROCEDURE_NAME
String
The procedure name.
PROCEDURE_OWNER
String
The procedure owner.
PROCEDURE_TYPE
String
This is one of the following:
UNKNOWN: It is not known whether a value is returned.
PROCEDURE: Procedure; no value is returned.
FUNCTION: Function; a value is returned.
1 All classes are System.XXX. For example, System.String.

TablePrivileges Schema Collection
Description: The TablePrivileges schema collection identifies the privileges on tables defined in the catalog that are available to or granted by a given user.
Number of restrictions: 3
Restrictions available: TABLE_CATALOG, TABLE_NAME, GRANTEE
Sort order: TABLE_CATALOG, TABLE_NAME, PRIVILEGE_TYPE
 
Column Name
Type Indicator1
Description
GRANTEE
String
The user name (or PUBLIC) to whom the privilege has been granted.
PRIVILEGE_TYPE
String
The privilege type. This is one of the following types:
• DELETE
• INSERT
• REFERENCES
• SELECT
• UPDATE
TABLE_CATALOG
String
The name of the database in which the table is defined.
TABLE_NAME
String
The table name.
TABLE_OWNER
String
The table owner.
1 All classes are System.XXX. For example, System.String.

Tables Schema Collection
Description: The Tables schema collection identifies the tables (including views) defined in the catalog that are accessible to a given user.
Number of Restrictions: 3
Restrictions Available: TABLE_CATALOG, TABLE_NAME, TABLE_TYPE
Sort order: TABLE_TYPE, TABLE_CATALOG, TABLE_NAME
 
Column Name
.NET Framework DataType1
Description
DESCRIPTION
String
A description of the table.
If no description is associated with the column, the data provider returns DbNull.
TABLE_CATALOG
String
The name of the database in which the table is defined.
TABLE_NAME
String
The table name.
TABLE_OWNER
String
The table owner.
TABLE_TYPE
String
The table type. One of the following:
• ALIAS
• GLOBAL TEMPORARY
• LOCAL TEMPORARY
• SYNONYM
• SYSTEM TABLE
• SYSTEM VIEW
• TABLE
• VIEW
This column cannot contain an empty string.
1 All classes are System.XXX. For example, System.String.

Views Schema Collection
Description: The Views schema collection identifies the views defined in the catalog that are accessible to a given user.
Number of restrictions: 2
Restrictions available: TABLE_CATALOG, TABLE_NAME
Sort order: TABLE_CATALOG, TABLE_NAME
 
Column Name
Type Indicator1
Description
TABLE_CATALOG
String
The name of the database in which the table is defined.
TABLE_NAME
String
The table name.
TABLE_OWNER
String
The table owner.
TABLE_QUALIFIER
String
The table qualifier.
VIEW _DEFINITION
String
The view definition. This is a query expression.
1 All classes are System.XXX. For example, System.String.

Last modified date: 11/04/2024