Getting Schema Information

Finding and Returning Metadata for a Database

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 Table 50, in the order shown.

Table 50 Columns Returned by GetSchemaTable on PsqlDataReader
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's 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.

• MetaDataCollections Schema Collections

• DataSourceInformation Schema Collection

• DataTypes Collection

• ReservedWords Collection

• Restrictions Collection

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 Table 51 in any order.

Table 51 Columns Returned by the MetaDataCollections Schema Collection
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 Table 52, in any order. Note that only one row is returned.

Table 52 ColumnNames Returned by the DataSourceInformation Collection
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

Table 53 describes the supported columns of the DataTypes schema collection. The columns can be returned in any order.

Table 53 ColumnNames Returned by the DataTypes Collection
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. Table 54 describes the columns that the data provider supports.

Table 54 ReservedWords Schema Collection
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. Table 55 describes the columns returned by the data providers. The columns can be returned in any order.

The PSQL 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).

Table 55 ColumnNames Returned by the Restrictions 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 PSQL ADO.NET Data Provider supports the following additional schema collections:

• Columns Schema Collection

• ForeignKeys Schema Collection

• Indexes Schema Collection

• PrimaryKeys Schema Collection

• ProcedureParameters Schema Collection

• Procedures Schema Collection

• TablePrivileges Schema Collection

• Tables Schema Collection

• Views Schema Collection

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. Table 56 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

Table 56 Columns Schema Collection
Column Name	.NET Framework DataType¹	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's 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's 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's 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.

¹ 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

Table 57 ForeignKeys Schema Collection
Column Name	.NET Framework Datatype¹	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.
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.
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.

¹ 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

Table 58 Indexes Schema Collection
Column Name	.NET Framework DataType¹	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.
TYPE	String	The type of the index. This is 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

¹ 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

Table 59 PrimaryKeys Schema Collection
Column Name	.NET Framework DataType¹	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.

¹ 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

Table 60 ProcedureParameters Schema Collection
Column Name	.NET Framework DataType¹	Description
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's 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.
NUMERIC_PRECISION	Int32	If the column's data type is numeric, this is the maximum precision of the column. If the column's data type is not numeric, this is DbNull.
NUMERIC_SCALE	Int16	If the column's 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.
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.

¹ 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

Table 61 Procedures Schema Collection
Column Name	.NET Framework DataType¹	Description
PROCEDURE_CATALOG	String	The database name.
PROCEDURE_NAME	String	The procedure name.
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.

¹ 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

Table 62 TablePrivileges Schema Collection
Column Name	Type Indicator¹	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.

¹ 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

Table 63 Tables Schema Collection
Column Name	.NET Framework DataType¹	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_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.

¹ 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

Table 64 Views Schema Collection
Column Name	Type Indicator¹	Description
TABLE_CATALOG	String	The name of the database in which the table is defined.
TABLE_NAME	String	The table name.
VIEW _DEFINITION	String	The view definition. This is a query expression.

¹ All classes are System.XXX. For example, System.String.