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 95
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 the ODBC ones, you may refer to the ODBC documentation for additional information if you choose. This chapter provides enough information for you to understand and use the PSQL catalog functions, but does not provide exhaustive technical details.
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 (?).