Overview

Table 2-1 lists the catalog stored procedures that are covered in this chapter.

Table 2-1: Catalog stored procedures

Procedure

Description

sp_column_privileges

Returns permissions 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 the databases in Adaptive Server.

sp_datatype_info

Returns information about a particular datatype or about all supported datatypes.

sp_fkeys

Returns information about foreign key constraints created in the current database with the create table or alter table command.

sp_pkeys

Returns information about primary key constraints created for a single table with the create table or alter table command.

sp_server_info

Returns a list of Adaptive Server attribute names and current values.

sp_special_columns

Returns the optimal set of columns that uniquely identify a row in a table or view; can also return a list of the columns that are automatically updated when any value in the row is updated by a transaction.

sp_sproc_columns

Returns information about a stored procedure’s input and return parameters.

sp_statistics

Returns a list of indexes on a single table.

sp_stored_procedures

Returns information about one or more stored 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.

Catalog stored procedures retrieve information from the system tables in tabular form.

The catalog stored procedures, created by installmaster at installation, are located in the sybsystemprocs database and are owned by the System Administrator.

Many of them can be run from any database. If a catalog stored procedure is executed from a database other than sybsystemprocs, it retrieves information from the system tables in the database from which it was executed.

All catalog stored procedures execute at isolation level 1.

All catalog stored procedures report a return status. For example, this means that the procedure executed successfully. The examples in this book do not include the return status:

return status = 0