Zen Function1 | Returns |
---|---|
The list of columns and their corresponding information for a specified table, from the current database or the specified database | |
The foreign key information for the specified table in the current database | |
The primary key information for the specified table, from the current database or the database specified | |
The names of stored procedures in the current database or the specified database | |
The list of input and output parameters and the columns that make up the result set for the specified procedure | |
Information about the optimal set of columns that uniquely identifies a row in a specified table, or the columns that are automatically updated when any value in the row is updated by a transaction. | |
Statistics about a single table and the list of indexes associated with the table, from the current database or the specified database | |
A list of tables along with their corresponding information, from the current database or the specified database | |
1 Because the Zen catalog functions are based on ODBC, you may want to refer to ODBC documentation for additional information. The content presented here provides enough information to understand and use Zen catalog functions without exhaustive technical detail. |
Parameter | Type | Default Value | Description |
---|---|---|---|
database_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained. |
table_name | VARCHAR | (no default value) | Name of the table whose column information is required |
column_name | VARCHAR | All columns for the specified table | Column name of the table specified. |
Column Name | Data Type | Description |
---|---|---|
TABLE_QUALIFIER | VARCHAR | Name of the database. NULL if not applicable to the database. |
TABLE_OWNER | VARCHAR | Schema name of the table. NULL if not applicable to the database. |
TABLE_NAME | VARCHAR not NULL | Name of the table. |
COLUMN_NAME | VARCHAR | Column name of the table or an empty string for a column that does not have a name. |
DATA_TYPE | SMALLINT not NULL | SQL data type of the column. See Supported Data Types in ODBC Guide. |
TYPE_NAME | VARCHAR | Name of the data type of the column corresponding to DATA_TYPE value |
PRECISION | INTEGER | The precision of the column if the data type is Decimal, Numeric, and so forth. See Precision and Scale of Decimal Data Types. If DATA_TYPE is CHAR or VARCHAR, this column contains the maximum length in characters of the column. For date time data types, this is the total number of characters required to display the value when it is converted to characters. For numeric data types, this is either the total number of digits or the total number of bits allowed in the column, according to the RADIX column. |
LENGTH | INTEGER | The length in bytes of data transferred on a SQLGetData, SQLFetch, or SQLFetchScroll operation if SQL_C_DEFAULT is specified. For numeric data, this size may differ from the size of the data stored in the database. This value might differ from COLUMN_SIZE column for character data. |
SCALE | SMALLINT | The total number of significant digits to the right of the decimal point. For TIME, TIMESTAMP, and TIMESTAMP2, this column contains the number of digits in the fractional seconds component. For the other data types, this is the decimal digits of the column in the database. See Precision and Scale of Decimal Data Types. |
RADIX | SMALLINT | Base for numeric data types For numeric data types, either 10 or 2. •10—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of decimal digits allowed for the column. •2—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of bits allowed in the column. NULL is returned for data types where RADIX is not applicable. |
NULLABLE | SMALLINT | Indicates whether the procedure column accepts a NULL value: •0 = NO_NULLS—the procedure column does not accept NULL values. •1 = NULLABLE—the procedure column accepts NULL values. •2 = NULLABLE_UNKNOWN—it is not known if the procedure column accepts NULL values. |
REMARKS | VARCHAR | Remarks field |
COLUMN_DEF | VARCHAR | The default value of the column. If NULL was specified as the default value, this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, this column is NULL. |
SQL_DATA_TYPE | SMALLINT not NULL | Value of the SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor. This column is the same as the TYPE_NAME column, except for data types AUTOTIMESTAMP, DATE, DATETIME, TIME, TIMESTAMP, and TIMESTAMP2. For those data types, the SQL_DATA_TYPE field in the result set returns the following: SQL_DATE for DATE, SQL_TIME for TIME, and SQL_TIMESTAMP for AUTOTIMESTAMP, DATETIME, TIMESTAMP, and TIMESTAMP2. |
SQL_DATETIME_SUB | SMALLINT | Subtype code for AUTOTIMESTAMP, DATE, DATETIME, TIME, TIMESTAMP, and TIMESTAMP2. For other data types, this column returns a NULL. •1 = for DATE (SQL_CODE_DATE) •2 = for TIME (SQL_CODE_TIME) •3 = for AUTOTIMESTAMP, DATETIME, TIMESTAMP, and TIMESTAMP2 (SQL_CODE_TIMESTAMP) |
CHAR_OCTET_LENGTH | INTEGER | Maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL. |
ORDINAL_POSITION | INTEGER not NULL | For input and output parameters, the ordinal position of the parameter in the procedure definition (in increasing parameter order, starting at 1). For a return value (if any), 0 is returned. For result-set columns, the ordinal position of the column in the result set, with the first column in the result set being number 1. |
IS_NULLABLE | VARCHAR | "NO" if the column does not include NULLs. "YES" if the column includes NULLs. This column returns a zero-length string if nullability is unknown. The value returned for this column differs from the value returned for the NULLABLE column. |
Parameter | Type | Default Value | Description |
---|---|---|---|
table_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained. |
pkey_table_name | VARCHAR | (no default value) | Name of the table whose foreign key is associated with the primary key column. Pattern matching is supported (see String Search Patterns). |
fkey_table_name | VARCHAR | (no default value) | Name of the table whose foreign key information needs to be obtained. Pattern matching is supported (see String Search Patterns). |
Column Name | Data Type | Description |
---|---|---|
PKTABLE_QUALIFIER | VARCHAR | Database name of the primary key table. NULL if not applicable to the database. |
PKTABLE_OWNER | VARCHAR | Name of the owner of the primary key table. NULL if not applicable to the database. |
PKTABLE_NAME | VARCHAR not NULL | Name of the primary key table |
PKCOLUMN_NAME | VARCHAR not NULL | Column name of the primary key column. An empty string is returned for a column that does not have a name. |
FKTABLE_QUALIFIER | VARCHAR | Database name of the foreign key table. NULL if not applicable to the database. |
FKTABLE_OWNER | VARCHAR | Name of the owner of the foreign key table. NULL if not applicable to the database. |
FKTABLE_NAME | VARCHAR not NULL | Name of the foreign key table. |
FKCOLUMN_NAME | VARCHAR not NULL | Column name of the foreign key column. An empty string is returned for a column that does not have a name. |
KEY_SEQ | SMALLINT | Column sequence number in key (starting with 1). The value of this column corresponds to Xi$Part in X$Index. See X$Index. |
UPDATE_RULE | SMALLINT | Action to be applied to the foreign key when the SQL operation is UPDATE. Can have one of the following values: •0 = CASCADE •1 = RESTRICT |
DELETE_RULE | SMALLINT | Action to be applied to the foreign key when the SQL operation is DELETE. Can have one of the following values: •0 = CASCADE •1 = RESTRICT |
FK_NAME | VARCHAR | Name of the foreign key. NULL if not applicable to the database. |
PK_NAME | VARCHAR | Name of the primary key. NULL if not applicable to the database. |
DEFERRABILITY | SMALLINT | One of the following values: •5 = INITIALLY_DEFERRED •6 = INITIALLY_IMMEDIATE •7 = NOT_DEFERRABLE |
Parameter | Type | Default Value | Description |
---|---|---|---|
pkey_table_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained. |
table_name | VARCHAR | (no default value) | Name of the table whose primary key information is requested. Pattern matching is supported (see String Search Patterns). |
Column Name | Data Type | Description |
---|---|---|
TABLE_QUALIFIER | VARCHAR | Name of the database. NULL if not applicable to the database. |
TABLE_OWNER | VARCHAR | Name of the owner of the primary key table. NULL if not applicable to the database. |
TABLE_NAME | VARCHAR not NULL | Name of the primary key table |
COLUMN_NAME | VARCHAR not NULL | Name of the primary key column. An empty string is returned for a column that does not have a name. |
COLUMN_SEQ | SMALLINT not NULL | Column sequence number in key (starting with 1). |
PK_NAME | VARCHAR | Name of the primary key. NULL if not applicable to the database. |
Parameter | Type | Default Value | Description |
---|---|---|---|
database_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained. |
procedure_name | VARCHAR | (no default value) | Name of the stored procedure whose information is required. |
Column Name | Data Type | Description |
---|---|---|
PROCEDURE_QUALIFIER | VARCHAR | Name of the database in which the procedure was created. NULL if not applicable to the database. |
PROCEDURE_OWNER | VARCHAR | Procedure schema identifier. NULL if not applicable to the database. |
PROCEDURE_NAME | VARCHAR not NULL | Procedure identifier |
NUM_INPUT_PARAMS | none | Reserved for future use. Do not use for your application. |
NUM_OUTPUT_PARAMS | none | Reserved for future use. Do not use for your application. |
NUM_RESULT_SETS | none | Reserved for future use. Do not use for your application. |
REMARKS | VARCHAR | The description of the procedure |
PROCEDURE_TYPE | SMALLINT | Defines the procedure type: •0 = PT_UNKNOWN—it cannot be determined whether the procedure returns a value. •1 = PT_PROCEDURE—the returned object is a procedure and does not have a return value. •2 = PT_FUNCTION—the returned object is a function and has a return value. |
Parameter | Type | Default Value | Description |
---|---|---|---|
database_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained. |
procedure_name | VARCHAR | (no default value) | Name of the stored procedure whose information is required. |
procedure_column_name | VARCHAR | (no default value) | Name of the column in the procedure. |
Column Name | Data Type | Description |
---|---|---|
PROCEDURE_QUALIFIER | VARCHAR | Name of the database in which the procedure was created. NULL if not applicable to the database. |
PROCEDURE_OWNER | VARCHAR | Procedure schema identifier. NULL if not applicable to the database. |
PROCEDURE_NAME | VARCHAR not NULL | Procedure identifier |
COLUMN_TYPE | SMALLINT not NULL | Defines the procedure column as a parameter or a result set column: •0 = PARAM_TYPE_UNKNOWN—procedure column is a parameter whose type is unknown. •1 = PARAM_INPUT—procedure column is an input parameter. •2 = PARAM_INPUT_OUTPUT—procedure column is an input/output parameter. •3 = RESULT_COL—procedure column is a result set column. •4 = PARAM_OUTPUT—procedure column is an output parameter. •5 = RETURN_VALUE—procedure column is the return value of the procedure. |
DATA_TYPE | SMALLINT not NULL | SQL data type. See also Supported Data Types in ODBC Guide. |
TYPE_NAME | VARCHAR not NULL | Relational data type name. See also Zen Supported Data Types. |
PRECISION | INTEGER | Size of the procedure column in the database. NULL is returned for data types where column size is not applicable. See also Precision and Scale of Decimal Data Types. |
LENGTH | INTEGER | Length in bytes of data transferred on a SQLGetData or SQLFetch operation if SQL_C_DEFAULT is specified. For numeric data, this size may be different than the size of the data stored in the database. See also Zen Supported Data Types. |
SCALE | SMALLINT | Number of decimal digits of the procedure column in the database. NULL is returned for data types where decimal digits is not applicable. See also Precision and Scale of Decimal Data Types. |
RADIX | SMALLINT | For numeric data types, either 10 or 2. •10—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of decimal digits allowed for the column. •2—the values in COLUMN_SIZE and DECIMAL_DIGITS give the number of bits allowed in the column. NULL is returned for data types where RADIX is not applicable. |
NULLABLE | SMALLINT not NULL | Indicates whether the procedure column accepts a NULL value: •0 = NO_NULLS—the procedure column does not accept NULL values. •1 = NULLABLE—the procedure column accepts NULL values. •2 = NULLABLE_UNKNOWN—it is not known if the procedure column accepts NULL values. |
REMARKS | VARCHAR | The description of the procedure column |
COLUMN_DEF | VARCHAR | The default value of the column. If NULL was specified as the default value, this column is the word NULL, not enclosed in quotation marks. If the default value cannot be represented without truncation, this column contains TRUNCATED, with no enclosing single quotation marks. If no default value was specified, this column is NULL. |
SQL_DATA_TYPE | SMALLINT not NULL | Value of the SQL data type as it appears in the SQL_DESC_TYPE field of the descriptor. This column is the same as the TYPE_NAME column, except for data types AUTOTIMESTAMP, DATE, DATETIME, TIME, TIMESTAMP, and TIMESTAMP2. For those data types, the SQL_DATA_TYPE field in the result set returns the following: SQL_DATE for DATE, SQL_TIME for TIME, and SQL_TIMESTAMP for AUTOTIMESTAMP, DATETIME, TIMESTAMP, and TIMESTAMP2. |
SQL_DATETIME_SUB | SMALLINT | Subtype code for AUTOTIMESTAMP, DATE, DATETIME, TIME, TIMESTAMP, and TIMESTAMP2. For other data types, this column returns a NULL. •1 = for DATE (SQL_CODE_DATE) •2 = for TIME (SQL_CODE_TIME) •3 = for AUTOTIMESTAMP, DATETIME, TIMESTAMP, and TIMESTAMP2 (SQL_CODE_TIMESTAMP) |
CHAR_OCTET_LENGTH | INTEGER | Maximum length in bytes of a character or binary data type column. For all other data types, this column returns a NULL. |
ORDINAL_POSITION | INTEGER not NULL | For input and output parameters, the ordinal position of the parameter in the procedure definition (in increasing parameter order, starting at 1). For a return value (if any), 0 is returned. For result-set columns, the ordinal position of the column in the result set, with the first column in the result set being number 1. |
IS_NULLABLE | VARCHAR | "NO" if the column does not include NULLs. "YES" if the column includes NULLs. This column returns a zero-length string if nullability is unknown. The value returned for this column differs from the value returned for the NULLABLE column. |
Parameter | Type | Default Value | Description |
---|---|---|---|
database_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR | (no default value) | Name of the table whose column information is required |
nullable | SMALLINT | (no default value) | Determines whether to return special columns that can have a NULL value. Must be one of the following: •0 = NO_NULLS—exclude special columns that can have NULL values. •1 = NULLABLE—return special columns even if they can have NULL values. |
Column Name | Data Type | Description |
---|---|---|
SCOPE | SMALLINT | Scope of the rowid. Contains one of the following values: •0 = SCOPE_CURROW •1 = SCOPE_TRANSACTION •2 = SCOPE_SESSION NULL is returned when IdentifierType is SQL_ROWVER. |
COLUMN_NAME | VARCHAR not NULL | Name of the column. An empty string is returned for a column that does not have a name. |
DATA_TYPE | SMALLINT not NULL | SQL data type. See also Supported Data Types in ODBC Guide. |
PRECISION | INTEGER | Size of the procedure column in the database. See also Precision and Scale of Decimal Data Types. |
LENGTH | INTEGER | Length in bytes of data transferred on a SQLGetData or SQLFetch operation if SQL_C_DEFAULT is specified. For numeric data, this size may differ from that of the data stored in the database. See also Zen Supported Data Types. |
SCALE | SMALLINT | Number of decimal digits of the procedure column in the database. NULL is returned for data types where decimal digits is not applicable. See also Precision and Scale of Decimal Data Types. |
PSEUDO_COLUMN | SMALLINT | Indicates whether the column is a pseudo-column. •0 = PC_UNKNOWN Zen does not support pseudo-columns. |
Parameter | Type | Default Value | Description |
---|---|---|---|
database_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR | (no default value) | Name of the table whose column information is required. Pattern matching is supported (see String Search Patterns). |
unique | SMALLINT | (no default value) | Type of index: 0 = INDEX_UNIQUE 1 = INDEX_ALL |
Column Name | Data Type | Description |
---|---|---|
TABLE_QUALIFIER | VARCHAR | Name of the database containing the table to which the statistic or index applies. NULL if not applicable to the database. |
TABLE_OWNER | VARCHAR | Schema name of the table to which the statistic or index applies. NULL if not applicable to the database. |
TABLE_NAME | VARCHAR not NULL | Name of the table to which the statistic or index applies |
NON_UNIQUE | SMALLINT | Indicates whether the index does not allow duplicate values: •0 = FALSE if the index values must be unique. •1 = TRUE if the index values can be nonunique. NULL is returned if TYPE is TABLE_STAT. |
INDEX_QUALIFIER | VARCHAR | The identifier that is used to qualify the index name doing a DROP INDEX. NULL is returned if an index qualifier is not supported by the database or if TYPE is TABLE_STAT. |
INDEX_NAME | VARCHAR | Index name. NULL is returned if TYPE is TABLE_STAT. |
TYPE | SMALLINT not NULL | Type of information being returned: •0 = TABLE_STAT •3 = INDEX_OTHER |
SEQ_IN_INDEX | SMALLINT | Column sequence number in index (starting with 1). NULL is returned if TYPE is TABLE_STAT. |
COLUMN_NAME | VARCHAR | Column name. If the column is based on an expression, such as PERSONID + NAME, the expression is returned. Iff the expression cannot be determined, an empty string is returned. NULL is returned if TYPE is TABLE_STAT. |
COLLATION | CHAR | Sort sequence for the column: A = ascending D = descending NULL is returned if column sort sequence is not supported by the database or if TYPE is TABLE_STAT. |
CARDINALITY | INTEGER | Cardinality of table or index. Number of rows in table if TYPE is TABLE_STAT. Number of unique values in the index if TYPE is not TABLE_STAT NULL is returned if the value is not available from the database. |
PAGES | INTEGER | Number of pages used to store the index or table. Number of pages for the table if TYPE is TABLE_STAT. Number of pages for the index if TYPE is not TABLE_STAT NULL is returned if the value is not available from the database or if not applicable to the database. |
FILTER_CONDITION | VARCHAR | If the index is a filtered index, this is the filter condition, such as CLASSID > 150. If the filter condition cannot be determined, this is an empty string. NULL if the index is not a filtered index, it cannot be determined whether the index is a filtered index, or TYPE is TABLE_STAT. |
Parameter | Type | Default Value | Description |
---|---|---|---|
database_qualifier | VARCHAR | Current database | Name of the database from which the details are to be obtained |
table_name | VARCHAR | (no default value) | Name of the table whose information needs to be obtained. |
type | VARCHAR | (no default value) | Must be one of the following: •TABLE returns only the user tables •SYSTEM TABLE returns all the system tables •VIEW returns only views •NULL returns all tables |
Column Name | Data Type | Description |
---|---|---|
TABLE_QUALIFIER | VARCHAR | Name of the database. NULL if not applicable to the database. |
TABLE_OWNER | VARCHAR | Name of the table owner. NULL if not applicable to the database. |
TABLE_NAME | VARCHAR | Name of the table |
TABLE_TYPE | VARCHAR | One of the following: •TABLE •VIEW •SYSTEM TABLE |
REMARKS | VARCHAR | A description of the table. |
Parameter | Type | Default Value | Description |
---|---|---|---|
table_name | VARCHAR | (no default value) | Name of the table in the current database for which to obtain information. |
Column Name | Data Type | Description |
---|---|---|
Table Name | VARCHAR | Name of the table. |
Table Location | VARCHAR | Full path name of the data file for the table. |
Dictionary Path | VARCHAR | Dictionary path of the database. |
File Version | VARCHAR | Btrieve version of the file in hexadecimal, such as "13.0" for version 13. |
Record Length | SMALLINT | Fixed record length for the file, as returned by Stat (15). |
Page Size | SMALLINT | Page size for the file, as returned by Stat (15). |
Number of Records | BIGINT | Number of records in the file, as returned by Stat (15). |
Number of Indexes | SMALLINT | Number of indexes, as returned by Stat (15). |
Unused LinkedDup Ptr | SMALLINT | Number of unused duplicate pointers, as returned by Stat (15). |
Unused PreAlloc Pages | SMALLINT | Number of unused empty pages in the file, as returned by Stat (15). |
Variable Len Records | VARCHAR | YES or NO indicating whether the file contains variable length records. |
Blank Truncation | VARCHAR | YES or NO indicating whether variable length records use blank truncation. |
Record Compression | VARCHAR | YES or NO indicating whether Btrieve data compression is used. |
Page Compression | VARCHAR | YES or NO indicating whether Btrieve page compression is used. |
Key Only File | VARCHAR | YES or NO indicating whether the file is a key-only file. |
Index Balancing | VARCHAR | YES or NO indicating whether Btrieve index balancing is in use. |
Freespace Threshold | VARCHAR | Percentage indicating what the free space threshold is, if any. |
Uses ACS | VARCHAR | YES or NO indicating whether the file uses an alternate collating sequence. |
System Data | VARCHAR | YES or NO indicating whether the file has system data keys enabled. |
Used LinkedDup Ptr | SMALLINT | Number of keys using linked duplicates. |
Example Statement | Returns |
---|---|
SELECT * FROM dbo.fSQLStatistics ( null, ‘%’, 0 ) | All tables with a unique index in current database |
SELECT * FROM dbo.fSQLStatistics ( null, ‘t%’, 1 ) | All tables starting with ‘t’ and an index in current database |
SELECT * FROM dbo.fSQLPrimaryKeys ( null, ‘%’ ) | All tables with a primary key in current database |
SELECT * FROM dbo.fSQLPrimaryKeys ( null, ‘t%’ ) | All tables starting with ‘t’ and a primary key in current database |
SELECT * FROM dbo.fSQLForeignKeys ( null, ‘%’ , ‘%’ ) | All tables with a primary key and corresponding foreign key tables in current database |