System Catalog Functions
System catalog functions for retrieving metadata are covered in the following topics:
Zen System Catalog Functions
System catalog functions allow you to obtain database metadata from the data dictionary files, also known as the catalog. The system catalog functions can be used only in a FROM clause.
Zen can also return metadata by calling appropriate catalog APIs or by using system stored procedures (see System Stored Procedures). These two methods, however, do not store the metadata in a view that can be joined or unioned with other tables. To provide JOIN and UNION capability with other tables, the system catalog functions are required.
Note that some access methods, such as ADO.NET, require system catalog functions for entity support so that JOIN and UNION capabilities are available.
A temporary view schema for each system catalog function is created during the prepare phase and data is stored in the view by calling a corresponding catalog API during the execute phase.
The following table lists the available system catalog functions.
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. | |
Return Status
Each system catalog function returns one of the following status values depending on execution results:
• SQL_SUCCESS
• SQL_SUCCESS_WITH_INFO
• SQL_STILL_EXECUTING
• SQL_ERROR
• SQL_INVALID_HANDLE
Summary
The system catalog functions have the following characteristics:
• They return metadata.
• They work in the same manner as views.
• They can be referenced only in the FROM clause of a SELECT statement.
• The parameters can be only in the form of constants or dynamic parameters.
Note: Most popular SQL editors do not use statement delimiters to execute multiple statements. However, SQL Editor in ZenCC requires them. If you wish to execute the examples in other environments, you may need to remove the pound sign or semicolon separators.
dbo.fSQLColumns
This function returns the list of column names in a specified table.
Syntax
dbo.fSQLColumns <'database_qualifier' | null>, <'table_name' | null>, <'column_name' | null>)
Arguments
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. |
Returned Result Set
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, then 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. |
Example
This example returns information for all columns in the Room table in the default Demodata sample database.
SELECT * FROM dbo.fSQLColumns ('Demodata', 'room', null);
Result Set (abbreviated for space considerations):
COLUMN_NAME DATA_TYPE LENGTH ORDINAL_POSITION
============== ========= ====== ================
Building_Name 1 25 1
Number 4 4 2
Capacity 5 2 3
Type 1 20 4
4 rows were affected.
dbo.fSQLForeignKeys
This functions returns the foreign key information for the specified table in the current database. Dbo.fSQLForeignKeys can return a list of foreign keys as a result set for either of the following:
• The specified table (columns in the specified table that refer to primary keys in other tables)
• Other tables that refer to the primary key in the specified table
Syntax
dbo.fSQLForeignKeys (<'table_qualifier' | null>, 'pkey_table_name' | null>, <'fkey_table_name' | null>)
Arguments
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). |
Returned Result Set
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 |
Example
This example creates three tables in the Demodata sample database. Primary keys and foreign keys are assigned to the tables. The dbo.fSQLForeignKeys function references the two primary key tables using a string search pattern. See also String Search Patterns.
CREATE TABLE primarykey1 (pk1col1 INT, pk1col2 INT, pk1col3 INT, pk1col4 INT, PRIMARY KEY (pk1col1, pk1col2));
ALTER TABLE primarykey1 ADD FOREIGN KEY (pk1col3, pk1col4) REFERENCES primarykey1 ON DELETE CASCADE;
CREATE TABLE primarykey2 (pk2col1 INT, pk2col2 INT, pk2col3 INT, pk2col4 INT, PRIMARY KEY (pk2col1, pk2col2));
ALTER TABLE primarykey2 ADD FOREIGN KEY (pk2col3, pk2col4) REFERENCES primarykey2 ON DELETE CASCADE;
CREATE TABLE foreignkey1 (fkcol1 INT, fkcol2 INT, fkcol3 INT, fkcol4 INT);
ALTER TABLE foreignkey1 ADD FOREIGN KEY (fkcol1, fkcol2) REFERENCES PRIMARYKEY1;
ALTER TABLE foreignkey1 ADD FOREIGN KEY (fkcol3, fkcol4) REFERENCES PRIMARYKEY2;
SELECT * FROM dbo.fSQLForeignKeys ('Demodata', 'primarykey%', 'foreignkey1');
Result Set (abbreviated for space considerations):
FKCOLUMN_NAME DELETE_RULE FK_NAME PK_NAME
============= =========== ========== ==========
fkcol1 1 FK_0fkcol1 PK_pk1col1
fkcol2 1 FK_0fkcol1 PK_pk1col1
fkcol3 1 FK_0fkcol3 PK_pk2col1
fkcol4 1 FK_0fkcol3 PK_pk2col1
4 rows were affected.
dbo.fSQLPrimaryKeys
This function returns as a result set the column names that make up the primary key for a table. Dbo.fSQLPrimaryKeys does not support returning primary keys from multiple tables in a single call.
Syntax
dbo.fSQLPrimaryKeys (<'pkey_table_qualifier' | null>, <'table_name' | null>)
Arguments
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). |
Returned Result Set
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. |
Example
This example creates two tables in the Demodata sample database. Primary keys and foreign keys are assigned to the tables. The dbo.fSQLPrimaryKeys function references the two tables using a string search pattern. See also String Search Patterns.
CREATE TABLE tblprimarykey3 ( tblpk3col1 INT, tblpk3col2 INT, tblpk3col3 INT, tblpk3col4 INT, PRIMARY KEY (tblpk3col1, tblpk3col2) );
ALTER TABLE tblprimarykey3 ADD FOREIGN KEY (tblpk3col3, tblpk3col4) REFERENCES tblprimarykey3 ON DELETE CASCADE;
CREATE TABLE tblprimarykey4 ( tblpk4col1 INT, tblpk4col2 INT, tblpk4col3 INT, tblpk4col4 INT, PRIMARY KEY (tblpk4col1, tblpk4col2) );
ALTER TABLE tblprimarykey4 ADD FOREIGN KEY (tblpk4col3, tblpk4col4) REFERENCES tblprimarykey4 ON DELETE CASCADE;
SELECT * FROM dbo.fsqlprimarykeys('Demodata', 'tbl%');
Result Set (abbreviated for space considerations):
TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
============== =========== ======= =============
tblprimarykey3 tblpk3col1 1 PK_tblpk3col1
tblprimarykey3 tblpk3col2 2 PK_tblpk3col1
tblprimarykey4 tblpk4col1 1 PK_tblpk4col1
tblprimarykey4 tblpk4col2 2 PK_tblpk4col1
4 rows were affected.
dbo.fSQLProcedures
This function returns as a result set the names of stored procedures and user-defined functions in the current database or the specified database. See also CREATE PROCEDURE and CREATE FUNCTION.
Syntax
dbo.fSQLProcedures (<'database_qualifier' | null>, <'procedure_name' | null>)
Arguments
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. |
Note: System stored procedures are defined in the internal PERVASIVESYSDB database, which Zen Control Center does not display.
Returned Result Set
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. |
Example
By default, the Demodata database does not contain any stored procedures or user-defined functions. To provide output for the dbo.fSQLProcedures function (and for dbo.fSQLProcedureColumns), you can create the following stored procedures and user-defined function. They can all be called provided the required tables and parameter bindings are present. However, the point for this example is to include them as database objects rather than demonstrate their execution.
See also CREATE PROCEDURE and CREATE FUNCTION.
CREATE PROCEDURE curs1 (IN :Arg1 CHAR(4), IN :Arg2 INTEGER) AS BEGIN
DECLARE :alpha char(10) DEFAULT 'BA';
DECLARE :beta INTEGER DEFAULT 100;
DECLARE degdel CURSOR FOR
SELECT degree, cost_per_credit FROM tuition WHERE Degree = :Arg1 AND cost_per_credit = :arg2
FOR UPDATE;
OPEN degdel;
FETCH NEXT FROM degdel INTO :alpha, :beta
DELETE WHERE CURRENT OF degdel;
CLOSE degdel ;
END
CREATE PROCEDURE EnrollStudent2 (IN :Stud_id INTEGER, IN
:Class_Id INTEGER);
BEGIN
INSERT INTO Enrolls VALUES (:Stud_id, :Class_Id, 0.0);
END
CREATE PROCEDURE AInsert
(IN :AGUID BINARY(16),
IN :APeriod INT,
IN :BBal UTINYINT,
IN :BDr DECIMAL(23,9),
IN :BCr DECIMAL(23,9),
IN :BNet DECIMAL(23,9),
IN :HTrx UTINYINT,
IN :PDr DECIMAL(23,9),
IN :PCr DECIMAL(23,9),
IN :PNet DECIMAL(23,9))
AS BEGIN
INSERT INTO "ASum" ("AID", "APeriod", "IBal", "BDr", "BCr", "BNet", "HTrx", "PDr", "PCr", "PNet") VALUES (:AGUID,:APeriod,:BBal,:BDr,:BCr,:BNet,:HTrx, :PDr,:PCr,:PNet);
END
CREATE PROCEDURE AR (OUT :BIID SMALLINT, IN :BName CHAR(30))
AS BEGIN
SELECT MAX(BID) + 1 INTO :BIID FROM Br;
INSERT INTO Br (BID, FName) VALUES (:BIID, :BName);
END
CREATE FUNCTION CalInterest (IN :principle FLOAT,
IN :period REAL, IN :rate DOUBLE)
RETURNS DOUBLE
AS BEGIN
DECLARE :interest DOUBLE;
SET :interest = ((:principle * :period * :rate) /
100);
RETURN (:interest);
END;
SELECT * FROM dbo.fSQLProcedures ('Demodata', null);
Result Set (abbreviated for space considerations):
PROCEDURE_QUALIFIER PROCEDURE_NAME PROCEDURE_TYPE
=================== ================= ==============
Demodata curs1 1
Demodata Enrollstudent2 1
Demodata AInsert 1
Demodata AR 1
Demodata CalInterest 2
5 rows were affected.
dbo.fSQLProcedureColumns
This function returns the list of input and output parameters and the columns that make up the result set for the specified stored procedure or user-defined function. See also CREATE PROCEDURE and CREATE FUNCTION.
Syntax
dbo.fSQLProcedureColumns (<'database_qualifier' | null>, <'procedure_name' | null>, <'procedure_column_name' | null>)
Arguments
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. |
Note: System stored procedures are defined in the internal PERVASIVESYSDB database, which does not display in Zen Control Center.
Returned Result Set
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. |
Example
By default, the Demodata sample database does not contain any stored procedures or user-defined functions. To provide output for the dbo.fSQLProcedureColumns function, you can create the stored procedures and user-defined function provided in the example for dbo.fSQLProcedures. This example assumes that Demodata contains the stored procedures curs1, Enrollstudent2, AInsert, and AR, and the user-defined function CalInterest.
The following statement returns information for all columns in all stored procedures and user-defined functions in the Demodata sample database:
SELECT * FROM dbo.fsqlprocedurecolumns ('Demodata', null, null)
Result Set (abbreviated for space considerations):
PROCEDURE_NAME COLUMN_NAME COLUMN_TYPE DATA_TYPE
============== =========== =========== =========
AInsert :AGUID 1 -2
AInsert :APeriod 1 4
AInsert :BBal 1 -6
AInsert :BCr 1 3
AInsert :BDr 1 3
AInsert :BNet 1 3
AInsert :HTrx 1 -6
AInsert :PCr 1 3
AInsert :PDr 1 3
AInsert :PNet 1 3
AR :BIID 4 5
AR :BName 1 1
CalInterest :period 1 7
CalInterest :principle 1 8
CalInterest :rate 1 8
CalInterest :RETURN_VALUE 5 8
curs1 :Arg1 1 1
curs1 :Arg2 1 4
Enrollstudent2 :Class_Id 1 4
Enrollstudent2 :Stud_id 1 4
20 rows were affected.
dbo.fSQLSpecialColumns
For a specified table, this function retrieves column information for the optimal set of columns that uniquely identifies a row in the table and columns that are automatically updated when any value in the row is updated by a transaction.
Syntax
dbo.fSQLSpecialColumns (<'database_qualifier' | null>, <'table_name' | null>, <'nullable' | null>)
Arguments
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. |
Returned Result Set
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. |
Example
This example creates a table with two columns that uniquely identify a row and are automatically updated when a transaction updates any value in the row.
CREATE TABLE t2 (c1 IDENTITY, c2 INTEGER, c3 SMALLINT NOT NULL, c4 TIMESTAMP NOT NULL)
ALTER TABLE t2 ADD PRIMARY KEY (c1, c4);
SELECT * FROM dbo.fSQLSpecialColumns ('Demodata' ,'t2' , 'null')
Result Set (abbreviated for space considerations):
COLUMN_NAME DATA_TYPE TYPE_NAME PRECISION LENGTH
=========== ========= ========= ========= ======
c1 4 INTEGER 4 4
c4 11 DATETIME 16 16
2 rows were affected.
dbo.fSQLStatistics
This function returns as a result set a list of statistics about a table and the indexes associated with the table.
Syntax
dbo.fSQLStatistics (<'database_qualifier' | null>, <'table_name' | null>, <'unique' | null>)
Arguments
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 |
Returned Result Set
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. |
Example
This example returns statistics about all indexes for all tables that begin with the letter c in the default Demodata sample database. NULLs are excluded from INDEX_NAME. See also String Search Patterns.
SELECT * FROM dbo.fSQLStatistics ('Demodata', 'c%', 1) WHERE INDEX_NAME IS NOT NULL
Result Set (abbreviated for space considerations):
TABLE_NAME INDEX_NAME COLUMN_NAME
========== ================ ====================
Class UK_ID ID
Class Class_Name Name
Class Class_Name Section
Class Class_seg_Faculty Faculty_ID
Class Class_seg_Faculty Start_Date
Class Class_seg_Faculty Start_Time
Class Building_Room Building_Name
Class Building_Room Room_Number
Class Building_Room Start_Date
Class Building_Room Start_Time
Course Course_Name Name
Course DeptName Dept_Name
12 rows were affected.
dbo.fSQLTables
The function returns the list of table, catalog, or schema names, and table types, stored in a database.
Syntax
dbo.fSQLTables (<'database_qualifier' | null>, <'table_name' | null>, <['type' | null>)
Arguments
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 |
Returned Result Set
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. |
Example
This example returns a list of the user tables and system tables in the default Demodata sample database.
SELECT * FROM dbo.fSQLTables ('Demodata', null, null)
Result Set (abbreviated for space considerations):
TABLE_NAME TABLE_TYPE
============= ==============
X$File SYSTEM TABLE
X$Field SYSTEM TABLE
X$Index SYSTEM TABLE
X$View SYSTEM TABLE
X$Proc SYSTEM TABLE
X$Relate SYSTEM TABLE
X$Trigger SYSTEM TABLE
X$Attrib SYSTEM TABLE
X$Occurs SYSTEM TABLE
X$Variant SYSTEM TABLE
Billing TABLE
Class TABLE
Course TABLE
Dept TABLE
Enrolls TABLE
Faculty TABLE
Person TABLE
Room TABLE
Student TABLE
Tuition TABLE
X$User SYSTEM TABLE
X$Rights SYSTEM TABLE
22 rows were affected.
dbo.fSQLDBTableStat
This function returns as a result set the basic information, including the details returned by a Btrieve Stat (15) operation, about a table or file in the current database.
Syntax
dbo.fSQLDBTableStat ('table_name')
Argument
Parameter | Type | Default Value | Description |
|---|---|---|---|
table_name | VARCHAR | (no default value) | Name of the table in the current database for which to obtain information. |
Returned Result Set
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 Btrieve data compression. |
Page Compression | VARCHAR | YES or NO indicating Btrieve page compression. |
Key Only File | VARCHAR | YES or NO indicating whether the file is a key-only file. |
Index Balancing | VARCHAR | YES or NO indicating Btrieve index balancing. |
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. |
For further information on these fields, see the Create (14) and Stat (15) in Btrieve API Guide.
Example
This example returns information for a table in the default Demodata sample database.
SELECT * FROM dbo.fSQLDBTableStat ('student')
Result Set (reformatted for space considerations):
Table Name Student
Table Location C:\PROGRAMDATA\ACTIAN\ZEN\DEMODATA\Student.mkd
Dictionary Path C:\PROGRAMDATA\ACTIAN\ZEN\DEMODATA
File Version 9.5
Record Length 76
Page Size 4096
Number of Records 1288
Number of Indexes 2
Unused LinkedDup Ptr 0
Unused PreAlloc Pages 0
Variable Len Records NO
Blank Truncation NO
Record Compression NO
Page Compression NO
Key Only File NO
Index Balancing NO
Freespace Threshold 0%
Uses ACS NO
System Data YES
Used LinkedDup Ptr 0
String Search Patterns
The following system catalog functions support string search patterns:
Two wildcard characters can be used in a search pattern:
• Percent sign (%) represents any sequence of n characters.
• Underscore (_) represents a single character.
Examples
The following table lists examples of using string search patterns.
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 |
Last modified date: 08/05/2026