Catalog Stored Procedures

Catalog stored procedures retrieve information from the system tables in tabular form. Created by installmaster at installation, they 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.

return status = 0

• Specifying Optional Parameters
  If a parameter value for a catalog stored procedure contains punctuation or embedded blanks, or is a reserved word, you must enclose it in single or double quotes. If the parameter is an object name qualified by a database name or owner name, enclose the entire name in single or double quotes.
• Pattern Matching
  SAP ASE supports wildcards _, %, [], and [^]. For maximum interoperability, use only the % and _ wildcard characters as defined by ANSI SQL Standards.
• System Procedure Tables
  The catalog stored procedures sp_columns, sp_datatype_info, sp_special_columns, and sp_sproc_columns use the catalog stored procedure tables spt_datatype_info, spt_datatype_info_ext, and spt_server_info in the sybsystemprocs database to convert internal system values such as status bits into human-readable format.
• ODBC Datatypes
  These two tables list the datatype code numbers and matching datatype names returned by sp_columns and sp_column_privileges in the data_type column. The source for the description is the Open Database Connectivity (ODBC) Application Programming Interface (API).
• 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 databases in the SAP ASE server.
• sp_datatype_info
  Returns information about a particular ODBC datatype or about all ODBC datatypes.
• sp_fkeys
  Returns information about foreign key constraints created with the create table or alter table command in the current database.
• sp_pkeys
  Returns information about primary key constraints created with the create table or alter table command for a single table.
• sp_server_info
  Returns a list of SAP ASE 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 timestamp columns, with values that are automatically generated 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.