Developer Reference > Data Access Methods > SQL Engine Reference > System Catalog Functions
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
dbo.fSQLColumns
The list of columns and their corresponding information for a specified table, from the current database or the specified database
dbo.fSQLForeignKeys
The foreign key information for the specified table in the current database
dbo.fSQLPrimaryKeys
The primary key information for the specified table, from the current database or the database specified
dbo.fSQLProcedures
The names of stored procedures in the current database or the specified database
dbo.fSQLProcedureColumns
The list of input and output parameters and the columns that make up the result set for the specified procedure
dbo.fSQLSpecialColumns
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.
dbo.fSQLStatistics
Statistics about a single table and the list of indexes associated with the table, from the current database or the specified database
dbo.fSQLTables
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: 11/04/2024