Collection Contents Index System extended stored procedures CHAPTER 15.  System Tables pdf/chap14.pdf

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.

Top of page  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

Top of page  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

Top of page  


Collection Contents Index System extended stored procedures CHAPTER 15.  System Tables pdf/chap14.pdf