Additional 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 55 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 55

Columns Schema Collection

.NET Framework DataType¹

CHARACTER_MAXIMUM_ LENGTH

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

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.

The default value of the column.

COLUMN_HASDEFAULT

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.

The name of the column; this might not be unique.

The indicator of the column's data type.

This value cannot be null.

TRUE: The column might be nullable.

FALSE: The column is known not to be nullable.

NATIVE_DATA_TYPE

The data source description of the type.

This value cannot be null.

NUMERIC_PRECISION

If the column's data type is of numeric data, this is the maximum precision of the column.

NUMERIC_PRECISION_RADIX

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.

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

The ordinal of the column. Columns are numbered starting from one.

PROVIDER_DEFINED_TYPE

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

The provider-defined type of the column is mapped to the System.Data.DbType enumeration.

This value cannot be null.

The database name.

The table name.

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

Table 56

ForeignKeys Schema Collection

.NET Framework Datatype¹

The deferrability of the foreign key. The value is one of the following:

•

INITIALLY DEFERRED

•

INITIALLY IMMEDIATE

•

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.

The foreign key column name.

The foreign key name. This is a required restriction.

FK_TABLE_CATALOG

The catalog name in which the foreign key table is defined.

The foreign key table name. This is a required restriction.

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.

The primary key column name.

The primary key name.

PK_TABLE_CATALOG

The catalog name in which the primary key table is defined.

The primary key table name.

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

Table 57

Indexes Schema Collection

.NET Framework DataType¹

The number of unique values in the index.

This is one of the following:

ASC: The sort sequence for the column is ascending.

DESC: The sort sequence for the column is descending.

The column name.

FILTER_CONDITION

The WHERE clause that identifies the filtering restriction.

The catalog name.

The index name.

ORDINAL_POSITION

The ordinal position of the column in the index, starting with 1.

The number of pages used to store the index.

The catalog name.

The table name.

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.

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

Table 58

PrimaryKeys Schema Collection

.NET Framework DataType¹

The primary key column name.

The order of the column names in the key.

The primary key name.

The database name in which the table is defined.

The table name.

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

Table 59

ProcedureParameters Schema Collection

.NET Framework DataType¹

CHARACTER_OCTET_LENGTH

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.

The indicator of the column's data type.

This value cannot be null.

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.

TRUE: The parameter might be nullable.

FALSE: The parameter is not nullable.

NATIVE_DATA_TYPE

The data source description of the type.

This value cannot be null.

NUMERIC_PRECISION

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.

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

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

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

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.

The parameter name. If the parameter is not named, this is DbNull.

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

The catalog name.

The procedure name.

PROVIDER_DEFINED_TYPE

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

The data source defined type of the column as mapped to the System.Data.DbType enumeration.

This value cannot be null.

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

Table 60

Procedures Schema Collection

.NET Framework DataType¹

PROCEDURE_CATALOG

The database name.

The procedure name.

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

Table 61

TablePrivileges Schema Collection

Type Indicator¹

The user name (or PUBLIC) to whom the privilege has been granted.

The privilege type. This is one of the following types:

•

•

•

•

•

The name of the database in which the table is defined.

The table name.

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

Table 62

Tables Schema Collection

.NET Framework DataType¹

A description of the table.

If no description is associated with the column, the data provider returns DbNull.

The name of the database in which the table is defined.

The table name.

The table type. One of the following:

•

•

GLOBAL TEMPORARY

•

LOCAL TEMPORARY

•

•

•

•

•

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

Table 63

Views Schema Collection

Type Indicator¹

The name of the database in which the table is defined.

The table name.

VIEW _DEFINITION

The view definition. This is a query expression.

1

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