Chapter 1: System Procedures

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_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_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_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_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_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_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_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_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_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_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_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_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_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_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, 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_ldapadmin	Creates or lists an LDAP URL search string; verifies an LDAP URL search string or login.
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_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_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_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_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_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_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_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_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_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_set_qplan	Changes the text of the abstract plan of an existing plan without changing the associated query.
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_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_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_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_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_who	Reports information about all current Adaptive Server users and processes or about a particular user or process.

View this book as PDF