List of system procedures

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

Table 1-1: System procedures

Procedure

Description

sp_activeroles

Displays all active roles granted to a user’s login.

sp_add_qpgroup

Adds an abstract plan group.

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_add_time_range

Adds a named time range to Adaptive Server.

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_addremotelogin

Authorizes a new remote server user by adding an entry to master.dbo.sysremotelogins.

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_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_autoformat

Produces readable result set data, reformatting the width of variable-length character data to display only non-blank characters. Trailing blanks are truncated in the output.

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_client_addr

Displays the IP address of every Adaptive Server task with an attached client application, including the spid and the client host name.

sp_clusterlockusage

Cluster environment only Reports on the free, used, and retained locks in the cluster.

sp_cluster

Cluster environment only Performs various functions pertaining to a cluster environment.

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_compatmode

Verify whether full compatibility mode can be used.

sp_configure

Displays or changes configuration parameters.

sp_copy_all_qplans

Copies all plans for one abstract plan group to another group.

sp_copy_qplan

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, whether scrollable or non-scrollable.

sp_dbextend

Allows you to:

  • Install automatic database expansion procedures on database/segment pairs and devices.

  • Define site-specific policies for individual segments and devices.

  • Simulate execution of the database expansion machinery, to study the operation before engaging large volume loads.

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_defaultloc

Component Integration Services only Defines a default storage location for objects in a local database.

sp_deletesmobj

Tivoli Storage Manager license only Removes current server’s database backup objects from the IBM Tivoli Storage Manager.

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 device parameter settings 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_downgrade

Validates readiness for downgrade to an earlier 15.0.x release. Also downgrades the system catalog changes Adaptive Server 15.0.2 modified.

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_drop_qplan

Drops an abstract plan.

sp_drop_resource_limit

Removes one or more resource limits from Adaptive Server.

sp_drop_time_range

Removes a user-defined time range from Adaptive Server.

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_dropexternlogin

Component Integration Services only Drops the definition of a remote login previously defined by sp_addexternlogin.

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_dropremotelogin

Drops a remote user login.

sp_droprowlockpromote

Removes row lock promotion threshold values from a database or table.

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_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_encryption

Reports encryption information.

sp_engine

Enables you to bring an engine online or offline.

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_extengine

Starts and stops EJB Server. Displays status information about EJB Server.

sp_extrapwdchecks

A custom stored procedure that can contain user-defined logic for password complexity checks. You can configure sp_extrapwdchecks according to your security needs. Install sp_extrapwdchecks in the master database.

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_fixindex

Repairs the index on one of your system tables when it has been corrupted.

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_object

Provides access to an index previously marked suspect 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_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_help_qpgroup

Reports information on an abstract plan group.

sp_help_qplan

Reports information about an abstract plan.

sp_helpapptrace

Determines which sessions Adaptive Server is tracing. sp_helpapptrace returns the server process IDs (spids) for all the sessions Adaptive Server is tracing, the spids of the sessions tracing them, and the name of the tracefile.

sp_helpartition

Lists partition information for a specified table, index, or partition, or for all partitions in the database.

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_helpcomputedcolumn

Reports information on the computed columns in a specified table.

sp_helpconfig

Reports help information on configuration parameters.

sp_helpconstraint

Reports information about integrity constraints used in the specified tables.

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_helpremotelogin

Reports information about a particular remote server’s logins or about all remote servers’ logins.

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, and adds the number parameter, which is an integer identifying an individual procedure, when objname represents a group of procedures. This parameter tells sp_helptext to display the source text for a specified procedure in the group.

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_jreconfig

Manages the Java PCA/JVM, enabling arguments, directives, and configuration values, and changes, reports for sp_jreconfig and sp_pciconfig.

sp_ldapadmin

Creates or lists an LDAP URL search string; verifies an LDAP URL search string or login; specifies the access accounts and tunable LDAPUA-rel;ated parameters..

sp_listener

Dynamically starts and stops listeners on Adaptive Server on any given port on a per-engine basis.

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_lmconfig

Configures license management-related information on Adaptive Server. The configuration options set by sp_lmconfig are stored in the sylapi properties file.

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_logintrigger

Sets and displays the global login trigger. This global login trigger has the same characteristics as a personal login script. It is executed before any personal login script for every user that tries to log in, including system administrators and security officers.

sp_maplogin

Maps external users to Adaptive Server logins.

sp_metrics

Backs up, drops, and flushes QP metrics—always captured in the default running group, which is group 1 in each respective database—and their statistics on queries.

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_modifylogin

Modifies the default database, default language, default role activation, or full name for an Adaptive Server login account.

sp_modifystats

Allows the System Administrator to modify the density values of a column—or columns—in sysstatistics.

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_monitorconfig

Monitors more than 30 resources compared to the 6 resources it monitored in earlier versions.

sp_object_stats

Shows lock contention, lock wait-time, and deadlock statistics for tables and indexes.

sp_options

Show option values.

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_passwordpolicy

An interface that a user with sso_role can use to configure login and password policies. This information is stored in the master.dbo.sysattributes table.

sp_pciconfig

Manages the Java PCI Bridge, enabling arguments, directives, and configuration values.

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_post_xpload

Checks and rebuilds indexes after a cross-platform load database where the endian types are different.

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_procxmode

Displays or changes the transaction modes associated with stored procedures.

sp_querysmobj

Tivoli Storage Manager license only Queries the Tivoli Storage Manager for a list of the current server’s database backup objects.

sp_recompile

Causes each stored procedure and trigger that uses the named table to be recompiled the next time it runs.

sp_refit_admin

Cluster environment only Provides an interface to perform disk refit-related actions.

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_remotesql

Component Integration Services only Establishes a connection to a remote server, passes a query buffer to the remote server from the client, and relays the results back to the client.

sp_rename

Changes the name of a user-created object or user-defined datatype in the current database.

sp_rename_qpgroup

Renames an abstract plan group.

sp_renamedb

Changes the name of a database. You cannot rename system databases or databases with external referential integrity constraints.

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_set_qplan

Changes the text of the abstract plan of an existing plan without changing the associated query.

sp_setlangalias

Assigns or changes the alias for an alternate language.

sp_setpglockpromote

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_setrowlockpromote

Sets or changes row-lock promotion thresholds for a datarows-locked table, for all datarows-locked tables in a database, or for all datarows-locked tables on a server.

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_setup_table_transfer

Run once in each database containing the tables marked for incremental transfer to create the spt_TableTransfer table in this database.

sp_show_options

Prints all the server options that have been set in the current session. @@options the array of bits corresponding to server options. For every option, "low" is the byte number in @@options, and "high" is the bit within that byte corresponding to the option. If the bit is set, print name of that option.

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_spaceusage

Reports the space usage for a table, index, or transaction log and estimates the amount of fragmentation for tables and indexes in a database.

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_tab_suspectptn

Lists tables with suspect partitions. A range-partitioned table on character-based partition keys can become suspect after a sort-order change, and hash-partitioned tables can become suspect after a cross-platform dump load.

sp_tempdb

Creates the default temporary database group, binds temporary databases to the default temporary database group, binds users and applications to the default temporary database group or to specific temporary databases, and provides the binding interface for maintaining bindings in sysattributes that are related to the multiple temporary database.

sp_tempdb_markdrop

Cluster environment only Places a local system temporary database in the drop state.

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_tran_dumpable_status

If you cannot make a transaction dump on a database, sp_tran_dumpable_status displays the reasons the dump is not possible.

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_unbindexeclass

Removes the execution class attribute previously associated with an client application, login, or stored procedure for the specified scope.

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_version

Returns the version information of the installation scripts (installmaster, installdbccdb, and so on) that was last run and whether it was successful.

sp_volchanged

Notifies the Backup Server™ that the operator performed the requested volume handling during a dump or load.

sp_webservices

Creates and manages the proxy tables used in the Adaptive Server Web Services Engine.

sp_who

Reports information about all current Adaptive Server users and processes or about a particular user or process.