System Catalog Functions

Retrieving Metadata with System Catalog Functions

System catalog functions are covered the following topics:

• PSQL System Catalog Functions

• dbo.fSQLColumns

• dbo.fSQLForeignKeys

• dbo.fSQLPrimaryKeys

• dbo.fSQLProcedures

• dbo.fSQLProcedureColumns

• dbo.fSQLSpecialColumns

• dbo.fSQLStatistics

• dbo.fSQLTables

• String Search Patterns

PSQL System Catalog Functions

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

PSQL can also return metadata information by calling appropriate catalog APIs or by using system stored procedures (see System Stored Procedures). Those 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.

Table 94 System Catalog Functions
PSQL 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 PSQL 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 PSQL catalog functions without exhaustive technical detail.

Return Status

Each system catalog function can return one of the following statuses depending on the execution results:

•SQL_SUCCESS

•SQL_SUCCESS_WITH_INFO

•SQL_STILL_EXECUTING

•SQL_ERROR

•SQL_INVALID_HANDLE

Summary

The following characteristics apply to the system catalog functions:

•They return metadata information.

•They work in the same manner as views.

•They can be referenced only in a FROM clause of a SELECT statement.

•The parameters can be only in the form of constants or dynamic parameters (?).

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

Table 95 dbo.fSQLColumns 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

Table 96 dbo.fSQLColumns 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, this column contains the maximum length in characters of the column. For datetime 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 and TIMESTAMP, 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 DATE, TIME, DATETIME, and TIMESTAMP. 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 DATETIME and TIMESTAMP.
SQL_DATETIME_SUB	SMALLINT	Subtype code for DATE, TIME, DATETIME, and TIMESTAMP. For other data types, this column returns a NULL. •1 = for DATE (SQL_CODE_DATE) •2 = for TIME (SQL_CODE_TIME) •3 = for DATETIME and TIMESTAMP (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

Table 97 dbo.fSQLForeignKeys 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

Table 98 dbo.fSQLForeignKeys 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

Table 99 dbo.fSQLPrimaryKeys 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

Table 100 dbo.fSQLPrimaryKeys 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

Table 101 dbo.fSQLProcedures 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 does not display in PSQL Control Center.

Returned Result Set

Table 102 dbo.fProcedures 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 sample 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

Table 103 dbo.fSQLProcedureColumns 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 PSQL Control Center.

Returned Result Set

Table 104 dbo.fProcedureColumns 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 PSQL 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 PSQL 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 DATE, TIME, DATETIME, and TIMESTAMP. 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 DATETIME and TIMESTAMP.
SQL_DATETIME_SUB	SMALLINT	Subtype code for DATE, TIME, DATETIME, and TIMESTAMP. For other data types, this column returns a NULL. 1 = for DATE (SQL_CODE_DATE) 2 = for TIME (SQL_CODE_TIME) 3 = for DATETIME and TIMESTAMP (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

This function retrieves column information for a specified table for the following:

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

Table 105 dbo.fSQLSpecialColumns 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

Table 106 dbo.fSQLSpecialColumns 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 be different than the size of the data stored in the database. See also PSQL 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 PSQL 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

Table 107 dbo.fSQLStatistics 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

Table 108 dbo.fSQLStatistics 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 as a result set 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

Table 109 dbo.fSQLTables 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

Table 110 dbo.fSQLTables 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.

String Search Patterns

The following system catalog functions support string search patterns:

• dbo.fSQLForeignKeys

• dbo.fSQLPrimaryKeys

• dbo.fSQLStatistics

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