List of system procedures

Table 8-1 provides a brief description of each system procedure.

**Table 8-1: System procedures**
Procedure	Description
sp_activeroles	Displays all active roles granted to a user’s login.
sp_addalias	Allows an Adaptive Server user to be known in a database as another user.
sp_addauditrecord	Allows users to enter user-defined audit records (comments) into the audit trail.
sp_addaudittable	Adds another system audit table after auditing is installed.
sp_addengine	Adds an engine to an existing engine group or, if the group does not exist, creates an engine group and adds the engine.
sp_addexeclass	Creates or updates a user-defined execution class that you can bind to client applications, logins, and stored procedures.
sp_addextendedproc	Creates an extended stored procedure (ESP) in the master database.
sp_addexternlogin	Creates an alternate login account and password to use when communicating with a remote server through Component Integration Services.
sp_addgroup	Adds a group to a database. Groups are used as collective names in granting and revoking privileges.
sp_addlanguage	Defines the names of the months and days, and the date format, for an alternate language.
sp_addlogin	Adds a new user account to Adaptive Server.
sp_addmessage	Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls and by sp_bindmsg.
sp_addobjectdef	Specifies the mapping between a local table and an external storage location.
sp_add_qpgroup	Adds an abstract plan group.
sp_addremotelogin	Authorizes a new remote server user by adding an entry to master.dbo.sysremotelogins.
sp_add_resource_limit	Creates a limit on the amount of server resources that a login or application can use to execute a query, query batch, or transaction.
sp_addsegment	Defines a segment on a database device in the current database.
sp_addserver	Defines a remote server or defines the name of the local server.
sp_addthreshold	Creates a threshold to monitor space on a database segment. When free space on the segment falls below the specified level, Adaptive Server executes the associated stored procedure.
sp_add_time_range	Adds a named time range to Adaptive Server.
sp_addtype	Creates a user-defined datatype.
sp_addumpdevice	Adds a dump device to Adaptive Server.
sp_adduser	Adds a new user to the current database.
sp_altermessage	Enables and disables the logging of a specific system-defined or user-defined message in the Adaptive Server error log.
sp_audit	Allows a System Security Officer to configure auditing options.
sp_autoconnect	Defines a passthrough connection to a remote server for a specific user, which allows the named user to enter passthrough mode automatically at login.
sp_bindcache	Binds a database, table, index, text object, or image object to a data cache.
sp_bindefault	Binds a user-defined default to a column or user-defined datatype.
sp_bindexeclass	Associates an execution class with a client application, login, or stored procedure.
sp_bindmsg	Binds a user message to a referential integrity constraint or check constraint.
sp_bindrule	Binds a rule to a column or user-defined datatype.
sp_cacheconfig	Creates, configures, reconfigures, drops, and provides information about data caches.
sp_cachestrategy	Enables or disables prefetching (large I/O) and MRU cache replacement strategy for a table, index, text object, or image object.
sp_changedbowner	Changes the owner of a database.
sp_changegroup	Changes a user’s group.
sp_checknames	Checks the current database for names that contain characters not in the 7-bit ASCII set.
sp_checkreswords	Detects and displays identifiers that are Transact-SQL reserved words. Checks server names, device names, database names, segment names, user-defined datatypes, object names, column names, user names, login names, and remote login names.
sp_checksource	Checks for the existence of the source text of the compiled object.
sp_chgattribute	Changes the max_rows_per_page value for future space allocations of a table or index.
sp_clearpsexe	Clears the execution attributes of the client application, login, or stored procedure that was set by sp_setpsexe.
sp_clearstats	Initiates a new accounting period for all server users or for a specified user. Prints statistics for the previous period by executing sp_reportstats.
sp_cmp_all_qplans	Compares all abstract plans in two abstract plan groups.
sp_cmp_qplans	Compares two abstract plans.
sp_commonkey	Defines a common key—columns that are frequently joined—between two tables or views.
sp_companion	Performs cluster operations such as configuring Adaptive Server as a secondary companion in a high availability system and moving a companion server from one failover mode to another
sp_configure	Displays or changes configuration parameters.
sp_copy_all_qplans	Copies all plans for one abstract plan group to another group.
sp_copy_all_qplans	Copies one abstract plan to an abstract plan group.
sp_countmetadata	Displays the number of indexes, objects, or databases in Adaptive Server.
sp_cursorinfo	Reports information about a specific cursor or all cursors that are active for your session.
sp_dboption	Displays or changes database options.
sp_dbrecovery_order	Specifies the order in which user databases are recovered and lists the user-defined recovery order of a database or all databases.
sp_dbremap	Forces Adaptive Server to recognize changes made by alter database. Run this procedure only when instructed to do so by an Adaptive Server message.
sp_depends	Displays information about database object dependencies—the view(s), trigger(s), and procedure(s) that depend on a specified table or view, and the table(s) and view(s) that the specified view, trigger, or procedure depends on.
sp_deviceattr	Changes the dsync setting of an existing database device file.
sp_diskdefault	Specifies whether or not a database device can be used for database storage if the user does not specify a database device or specifies default with the create database or alter database commands.
sp_displayaudit	Displays the status of audit options.
sp_displaylevel	Sets or shows which Adaptive Server configuration parameters appear in sp_configure output.
sp_displaylogin	Displays information about a login account.
sp_displayroles	Displays all roles granted to another role, or displays the entire hierarchy tree of roles in table format.
sp_dropalias	Removes the alias user name identity established with sp_addalias.
sp_drop_all_qplans	Deletes all abstract plans in an abstract plan group.
sp_dropdevice	Drops an Adaptive Server database device or dump device.
sp_dropengine	Drops an engine from a specified engine group or, if the engine is the last one in the group, drops the engine group.
sp_dropexeclass	Drops a user-defined execution class.
sp_dropextendedproc	Removes an ESP from the master database.
sp_dropglockpromote	Removes lock promotion values from a table or database.
sp_dropgroup	Drops a group from a database.
sp_dropkey	Removes a key defined with sp_primarykey, sp_foreignkey, or sp_commonkey from the syskeys table.
sp_droplanguage	Drops an alternate language from the server and removes its row from master.dbo.syslanguages.
sp_droplogin	Drops an Adaptive Server user login by deleting the user’s entry in master.dbo.syslogins.
sp_dropmessage	Drops user-defined messages from sysusermessages.
sp_dropobjectdef	Component Integration Services only – Deletes the external storage mapping provided for a local object
sp_drop_qpgroup	Drops an abstract plan group.
sp_drop_qplan	Drops an abstract plan.
sp_dropremotelogin	Drops a remote user login.
sp_drop_resource_limit	Removes one or more resource limits from Adaptive Server.
sp_dropsegment	Drops a segment from a database or unmaps a segment from a particular database device.
sp_dropserver	Drops a server from the list of known servers.
sp_dropthreshold	Removes a free-space threshold from a segment.
sp_drop_time_range	Removes a user-defined time range from Adaptive Server.
sp_droptype	Drops a user-defined datatype.
sp_dropuser	Drops a user from the current database.
sp_dumpoptimize	Specifies the amount of data dumped by Backup Server during the dump database operation.
sp_estspace	Estimates the amount of space required for a table and its indexes, and the time needed to create the index.
sp_export_qpgroup	Exports all plans for a specified user and abstract plan group to a user table.
sp_extendsegment	Extends the range of a segment to another database device.
sp_familylock	Reports information about all the locks held by a family (coordinating process and its worker processes) executing a statement in parallel.
sp_find_qplan	Finds an abstract plan, given a pattern from the query text or plan text.
sp_flushstats	Flushes statistics from in-memory storage to the systabstats system table.
sp_forceonline_db	Provides access to all the pages in a database that were previously taken offline by recovery.
sp_forceonline_page	Provides access to pages previously taken offline by recovery.
sp_foreignkey	Defines a foreign key on a table or view in the current database.
sp_freedll	Unloads a dynamic link library (DLL) that was previously loaded into XP Server memory to support the execution of an ESP.
sp_getmessage	Retrieves stored message strings from sysmessages and sysusermessages for print and raiserror statements.
sp_grantlogin	Windows NT only – When Integrated Security mode or Mixed mode (with Named Pipes) is active, assigns Adaptive Server roles or default permissions to Windows NT users and groups.
sp_ha_admin	Performs administrative tasks on Adaptive Servers configured with Sybase Failover in a high availability system. sp_ha_admin is installed with the installhavss script (insthasv on Windows NT).
sp_help	Reports information about a database object (any object listed in sysobjects) and about Adaptive Server-supplied or user-defined datatypes.
sp_helpartition	Lists the first page and the control page for each partition in a partitioned table.
sp_helpcache	Displays information about the objects that are bound to a data cache or the amount of overhead required for a specified cache size.
sp_helpdb	Reports information about a particular database or about all databases.
sp_helpdevice	Reports information about a particular device or about all Adaptive Server database devices and dump devices.
sp_helpextendedproc	Displays ESPs registered in the current database, along with their associated DLL files.
sp_helpexternlogin	Component Integration Services only – Reports information about external login names.
sp_helpgroup	Reports information about a particular group or about all groups in the current database.
sp_helpindex	Reports information about the indexes created on a table.
sp_helpjava	Displays information about Java classes and associated JARs that are installed in the database.
sp_helpjoins	Lists the columns in two tables or views that are likely join candidates.
sp_helpkey	Reports information about a primary, foreign, or common key of a particular table or view, or about all keys in the current database.
sp_helplanguage	Reports information about a particular alternate language or about all languages.
sp_helplog	Reports the name of the device that contains the first page of the transaction log.
sp_helpobjectdef	Component Integration Services only – Reports information about remote object definitions. Shows owners, objects, type, and definition.
sp_help_qpgroup	Reports information on an abstract plan group.
sp_help_qplan	Reports information about an abstract plan.
sp_helpremotelogin	Reports information about a particular remote server’s logins or about all remote servers’ logins.
sp_help_resource_limit	Reports information about all resource limits, limits for a given login or application, limits in effect at a given time or day of the week, or limits with a given scope or action.
sp_helprotect	Reports information about permissions for database objects, users, groups, or roles.
sp_helpsegment	Reports information about a particular segment or about all segments in the current database.
sp_helpserver	Reports information about a particular remote server or about all remote servers.
sp_helpsort	Displays Adaptive Server’s default sort order and character set.
sp_helptext	Prints the text of a system procedure, trigger, view, default, rule, or integrity check constraint.
sp_helpthreshold	Reports the segment, free-space value, status, and stored procedure associated with all thresholds in the current database or all thresholds for a particular segment.
sp_helpuser	Reports information about a particular user or about all users in the current database.
sp_hidetext	Hides the source text for the specified compiled object.
sp_import_qpgroup	Imports abstract plans from a user table into an abstract plan group.
sp_indsuspect	Checks user tables for indexes marked as suspect during recovery following a sort order change.
sp_listsuspect_db	Lists all databases that have offline pages because of corruption detected on recovery.
sp_listsuspect_object	Lists all indexes in a database that are currently offline because of corruption detected on recovery.
sp_listsuspect_page	Lists all pages that are currently offline because of corruption detected on recovery.
sp_lock	Reports information about processes that currently hold locks.
sp_locklogin	Locks an Adaptive Server account so that the user cannot log in, or displays a list of all locked accounts.
sp_logdevice	Moves the transaction log of a database with log and data on the same device to a separate database device.
sp_loginconfig	Windows NT only – Displays the value of one or all integrated security parameters.
sp_logininfo	Windows NT only – Displays all roles granted to Windows NT users and groups with sp_grantlogin.
sp_logiosize	Changes the log I/O size used by Adaptive Server to a different memory pool when it is doing I/O for the transaction log of the current database.
sp_modifylogin	Modifies the default database, default language, default role activation, or full name for an Adaptive Server login account.
sp_modify_resource_limit	Changes a resource limit by specifying a new limit value or the action to take when the limit is exceeded, or both.
sp_modify_time_range	Changes the start day, start time, end day, and/or end time associated with a named time range.
sp_modifythreshold	Modifies a threshold by associating it with a different threshold procedure, free-space level, or segment name. You cannot use sp_modifythreshold to change the amount of free space or the segment name for the last-chance threshold.
sp_monitor	Displays statistics about Adaptive Server.
sp_object_stats	Shows lock contention, lock wait-time, and deadlock statistics for tables and indexes.
sp_passthru	Component Integration Services only – Allows the user to pass a SQL command buffer to a remote server.
sp_password	Adds or changes a password for an Adaptive Server login account.
sp_placeobject	Puts future space allocations for a table or an index on a particular segment.
sp_plan_dbccdb	Recommends suitable sizes for new dbccdb and dbccalt databases, lists suitable devices for dbccdb and dbccalt, and suggests a cache size and a suitable number of worker processes for the target database.
sp_poolconfig	Creates, drops, resizes, and provides information about memory pools within data caches.
sp_primarykey	Defines a primary key on a table or view.
sp_processmail	Windows NT only – Reads, processes, sends, and deletes messages in the Adaptive Server message inbox.
sp_procqmode	Displays the query processing mode of a stored procedure, view, or trigger.
sp_procxmode	Displays or changes the transaction modes associated with stored procedures.
sp_recompile	Causes each stored procedure and trigger that uses the named table to be recompiled the next time it runs.
sp_remap	Remaps a stored procedure, trigger, rule, default, or view from releases later than 4.8 and earlier than 10.0 to be compatible with releases 10.0 and later. Use sp_remap on pre-release 11.0 objects that the release 11.0 upgrade procedure failed to remap.
sp_remoteoption	Displays or changes remote login options.
sp_rename	Changes the name of a user-created object or user-defined datatype in the current database.
sp_renamedb	Changes the name of a database. You cannot rename system databases or databases with external referential integrity constraints.
sp_rename_qpgroup	Renames an abstract plan group.
sp_reportstats	Reports statistics on system usage.
sp_revokelogin	Windows NT only – When Integrated Security mode or Mixed mode (with Named Pipes) is active, revokes Adaptive Server roles and default permissions from Windows NT users and groups.
sp_role	Grants or revokes system roles to an Adaptive Server login account.
sp_sendmsg	Sends a message to a User Datagram Protocol (UDP) port.
sp_serveroption	Displays or changes remote server options.
sp_setlangalias	Assigns or changes the alias for an alternate language.
sp_setrowlockpromote	Sets or changes the lock promotion thresholds for a database, for a table, or for Adaptive Server.
sp_setpsexe	Sets custom execution attributes “on the fly” for an active client application, login, or stored procedure.
sp_set_qplan	Changes the text of the abstract plan of an existing plan without changing the associated query.
sp_setsuspect_granularity	Displays and sets the recovery fault isolation mode.
sp_setsuspect_threshold	On recovery, sets the maximum number of suspect pages that Adaptive Server will allow in the specified database before taking the entire database offline.
sp_showcontrolinfo	Displays information about engine group assignments, bound client applications, logins, and stored procedures.
sp_showexeclass	Displays the execution class attributes and the engines in any engine group associated with the specified execution class.
sp_showplan	Displays the query plan for any user connection for the current SQL statement (or a previous statement in the same batch). The query plan is displayed in showplan format.
sp_showpsexe	Displays execution class, current priority, and affinity for all processes running on Adaptive Server.
sp_spaceused	Displays estimates of the number of rows, the number of data pages, and the space used by one table or by all tables in the current database.
sp_ssladmin	Adds, deletes, or displays a list of server certificates for Adaptive Server.
sp_syntax	Displays the syntax of Transact-SQL statements, system procedures, utilities, and other routines, depending on which products and corresponding sp_syntax scripts exist on Adaptive Server.
sp_sysmon	Displays performance information.
sp_thresholdaction	Executes automatically when the number of free pages on the log segment falls below the last-chance threshold, unless the threshold is associated with a different procedure. Sybase does not provide this procedure.
sp_transactions	Reports information about active transactions.
sp_unbindcache	Unbinds a database, table, index, text object, or image object from a data cache.
sp_unbindcache_all	Unbinds all objects that are bound to a cache.
sp_unbindefault	Unbinds a created default value from a column or from a user-defined datatype.
sp_unbindefault	Unbinds a database, table, index, text object, or image object from a data cache.
sp_unbindmsg	Unbinds a user-defined message from a constraint.
sp_unbindrule	Unbinds a rule from a column or from a user-defined datatype.
sp_volchanged	Notifies the Backup Server™ that the operator performed the requested volume handling during a dump or load.
sp_who	Reports information about all current Adaptive Server users and processes or about a particular user or process.