Reference Manual
   CHAPTER 14. System Procedures and Functions     

Adaptive Server Enterprise system and catalog procedures

Adaptive Server Enterprise provides system and catalog procedures to carry out many administrative functions and to obtain system information. Anywhere has implemented support for some of these procedures.

System procedures are built-in stored procedures used for getting reports from and updating system tables. Catalog stored procedures retrieve information from the system tables in tabular form.

  Adaptive Server Enterprise system procedures

The following list describes the Adaptive Server Enterprise system procedures that are provided in Adaptive Server Anywhere.

While these procedures perform the same functions as they do in Adaptive Server Enterprise and pre-Version 12 Adaptive Server IQ, they are not identical. If you have preexisting scripts that use these procedures, you may want to examine the procedures. To see the text of a stored procedure, you can open it in Sybase Central or, in Interactive SQL, run the following command.

sp_helptext procedure_name

You may need to reset the width of your Interactive SQL output to see the full text, by selecting Command->Options and entering a new Limit Display Columns value.

System procedure	Description
sp_addgroup group-name	Adds a group to a database
sp_addlogin userid, password[, defdb [, deflanguage [, fullname]]]	Adds a new user account to a database
sp_addmessage message-num, message_text [, language]	Adds a user-defined message to SYSUSERMESSAGES, for use by stored procedure PRINT and RAISERROR calls
sp_addtype typename, data-type, [, "identity" | nulltype]	Creates a user-defined data type
sp_adduser login_name [, name_in_db [, grpname]]	Adds a new user to a database
sp_changegroup new-group-name, userid	Changes a user's group or adds a user to a group
sp_dboption [dbname, optname, {true | false}]	Displays or changes a database option
sp_dropgroup group-name	Drops a group from a database
sp_droplogin userid	Drops a user from a database
sp_dropmessage message-number [, language]	Drops a user-defined message
sp_droptype typename	Drops a user-defined data type
sp_dropuser userid	Drops a user from a database
sp_getmessage message-num, @msg-var output [, language]	Retrieves a stored message string from SYSMESSAGES or SYSUSERMESSAGES, for PRINT and RAISERROR statements.
sp_helptext object-name	Displays the text of a system procedure, trigger, or view
sp_password caller_passwd, new_passwd [, userid]	Adds or changes a password for a user ID

  Adaptive Server Enterprise catalog procedures

Adaptive Server Anywhere implements all the Adaptive Server Enterprise catalog procedures with the exception of the sp_column_privileges procedure. The implemented catalog procedures are described in the following table.

Catalog procedure	Description
sp_column_privileges	Unsupported
sp_columns table-name [, table-owner ] [, table-qualifier] [, column-name]	Returns the data types of the specified column
sp_fkeys pktable_name [, pktable-owner][, pktable-qualifier] [, fktable-name] [, fktable_owner] [, fktable-qualifier]	Returns foreign key information about the specified table
sp_pkeys table-name [, table_owner] [, table_qualifier]	Returns primary key information about the specified table
sp_special_columns table_name [, table-owner] [, table-qualifier] [, col-type]	Returns the optimal set of columns that uniquely identify a row in the specified table
sp_sproc_columns proc-name [, proc_owner] [, proc-qualifier] [, column-name]	Returns information about a stored procedure's input and return parameters
sp_stored_procedures [sp-name] [, sp-owner] [, sp-qualifier]	Returns information about one or more stored procedures
sp_tables table-name [, table-owner] [, table-qualifier] [, table-type]	Returns a list of objects that can appear in a FROM clause for the specified table