Home

	Expand Search

Reference Manual All Volumes (Online Only)

Chapter 3: System Procedures

Chapter 3

This chapter describes the system procedures, which are Sybase-supplied stored procedures used for updating and getting reports from system tables. Table 3-1 lists the system procedures discussed in this chapter.

Table 3-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	(Component Integration Services only) Assigns an alternate login name and password to be used when communicating with a remote server.
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	(Component Integration Services only) 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_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	(Component Integration Services only) 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_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_commonkey	Defines a common key¾columns that are frequently joined¾between two tables or views.
sp_configure	Displays or changes configuration parameters.
sp_countmetadata	Displays the number of user indexes, objects, or databases in a 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_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_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_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_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 to the local server 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_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_estspace	Estimates the amount of space required for a table and its indexes, and the time needed to create the index.
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_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_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_helpconfig	Reports help information about configuration parameters, such as how much memory is needed if the parameter is set to a certain value. sp_helpconfig displays the current setting, the amount of memory used for that setting, the default value, and the minimum and maximum settings.
sp_helpconstraint	Reports information about any integrity constraints specified for a table. This information includes the constraint name and the definition of the bound default, unique or primary key constraint, referential constraint, or check constraint. sp_helpconfig also displays the number of references used by 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_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_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_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_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_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_modify_time_range	Changes the start day, start time, end day, and/or end time associated with a named time range.
sp_monitor	Displays statistics about Adaptive Server.
sp_monitorconfig	Displays cache usage statistics regarding metadata descriptors for indexes, objects, and databases, such as the number of metadata descriptors currently in use by Adaptive Server. Also reports the number of auxiliary scan descriptors in use.
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_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_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_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_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_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_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_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	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(TM) 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.

Introduction to System Procedures

Manual Pages for System Procedures