Catalog Stored Procedures

Catalog stored procedure (CSPs) are specially recognized commands that return catalog information.

Client applications use CSPs instead of SQL to access information contained in the system catalog of the target database. The access service library implements CSPs by executing stored procedures against the target catalog.

When you invoke a CSP, the access service executes a stored procedure that returns a result set.

CSP attempts to match the results an Adaptive Server would return under the same circumstances. Because the ODBC catalog is significantly different from the Adaptive Server catalog, an exact match is impossible. To make the results match, the access service performs additional computations as the rows are returned.

Because some information is static, sp_datatype_info uses memory tables to improve operation speed.

You can execute CSPs using a language command or an RPC event.

When the access service processes a CSP as an RPC event, it retrieves the name and parameters from the client application using standard RPC processing techniques.

Syntax

CSP syntactical rules:

Arguments can be delimited with commas and identified by position:
```
sp_columns parm1,parm2
```
Arguments can be identified using the keyword NULL:
```
sp_columns NULL, NULL, parm3
```
Empty arguments can be identified using empty strings:
```
sp_columns ’ ’,’ ’,smith
```
Arguments can be named using the syntax @name=parm:
```
sp_columns @table_owner=smith
```

The positional forms (first three bullets) cannot be mixed with the named form (fourth bullet).

The access service library does not support the TABLE_QUALIFIER or PROCEDURE_QUALIFIER parameters. For all CSPs, leave the parameter empty or set it to NULL.

See the Adaptive Server Enterprise Reference Manual for details.

Treatment of Special Characters

The access service supports only the “%” (percent) wildcard character, which can be used in parameters that allow wildcard-character search patterns.

The character represents any string of zero or more characters.

The access service treats all underscore characters as literals.

ODBC Information
The ODBC definition of a CSP is the model, when the access service cannot support a particular procedure, it returns the expected column form descriptors, with no data rows.
sp_column_privileges
Returns column privilege information for one or more columns in a table or view.
sp_columns
Returns information about the type of data that can be stored in one or more columns.
sp_databases
Returns a list of databases on a target DBMS.
sp_datatype_info
Returns information about a particular datatype or all supported datatypes.
sp_fkeys
Returns primary and foreign key information for the specified table or tables.
sp_pkeys
Returns primary key information for a single table.
sp_server_info
Returns target server metadata containing a list of attribute names and matching values for the target.
sp_special_columns
Retrieves the information about columns within a specified table or view.
sp_sproc_columns
Returns information about stored procedure input and return parameters.
sp_statistics
Returns a list of indexes in a single table.
sp_stored_procedures
Returns a list of available procedures.
sp_table_privileges
Returns privilege information for all columns in a table or view.
sp_tables
Returns a list of objects that can appear in a from clause.