Home

	Expand Search

SQL Server Reference Manual

Chapter 6: System Procedures

Chapter 6

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

Table 6-1: System procedures

Procedure	Description
sp_addalias	Allows a SQL 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_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 for an alternate language, and its date format.
sp_addlogin	Adds a new user account to SQL Server.
sp_addmessage	Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls and by sp_bindmsg.
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, SQL Server executes the associated stored procedure.
sp_addtype	Creates a user-defined datatype.
sp_addumpdevice	Adds a dump device to SQL Server.
sp_adduser	Adds a new user to the current database.
sp_auditdatabase	Establishes auditing of different types of events within a database, or of references to objects within that database from another database.
sp_auditlogin	Audits a SQL Server user's attempts to access tables and views; audits the text of a user's command batches; lists users on which auditing is enabled; gives the auditing status of a user; or displays the status of table, view, or command text auditing.
sp_auditobject	Audits accesses to tables and views.
sp_auditoption	Enables or disables system-wide auditing and global audit options, or reports on the status of audit options.
sp_auditsproc	Audits the execution of stored procedures and triggers.
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_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	Enables or disables prefetching (large I/O) and MRU cache replacement strategy for a table, index, text object, or image object.
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. Do not change the owner of the sybsystemprocs 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_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_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 SQL Server to recognize changes made by alter database. Run this procedure only if instructed to do so by a SQL 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_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_dipsplaylevel	Sets or shows which SQL Server configuration parameters appear in sp_configure output.
sp_displaylogin	Displays information about a login account.
sp_dropalias	Removes the alias user name identity established with sp_addalias.
sp_dropdevice	Drops a SQL Server database device or dump device.
sp_dropglockpromote	Removes lock promotion values from a table or database.
sp_dropgroup	Drops a group from a database.
sp_dropkey	Removes from the syskeys table a key that had been defined using sp_primarykey, sp_foreignkey, or sp_commonkey.
sp_droplanguage	Drops an alternate language from the server and removes its row from master.dbo.syslanguages.
sp_droplogin	Drops a SQL Server user login by deleting the user's entry in master.dbo.syslogins.
sp_dropmessage	Drops user-defined messages from sysusermessages.
sp_dropremotelogin	Drops a remote user login.
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_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_foreignkey	Defines a foreign key on a table or view in the current database.
sp_getmessage	Retrieves stored message strings from sysmessages and sysusermessages for print and raiserror statements.
sp_grantlogin	When Integrated Security mode or Mixed mode (with Named Pipes) is active, assigns SQL 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 SQL 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_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_helpdb	Reports information about a particular database or about all databases.
sp_helpdevice	Reports information about a particular device or about all SQL Server database devices and dump devices.
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_helpremotelogin	Reports information about a particular remote server's logins or about all remote servers' logins.
sp_helprotect	Reports on permissions for database objects, users, or groups.
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 SQL 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_lock	Reports information about processes that currently hold locks.
sp_locklogin	Locks a SQL 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	Displays the value of one or all integrated security parameters.
sp_logininfo	Displays all roles granted to Windows NT users and groups with sp_grantlogin.
sp_logiosize	Changes the log I/O size used by SQL Server to a different memory pool when doing I/O for the transaction log of the current database.
sp_modifylogin	Modifies the default database, default language, or full name for a SQL Server login account.
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 SQL Server.
sp_password	Adds or changes a password for a SQL Server login account.
sp_placeobject	Puts future space allocations for a table or 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_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 prior to 10.0to 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 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	When Integrated Security mode or Mixed mode (with Named Pipes) is active, revokes SQL Server roles and default permissions from Windows NT users and groups.
sp_role	Grants or revokes roles to a SQL 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 SQL Server.
sp_spaceused	Displays 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 your server.
sp_sysmon	Displays performance information.
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_unbindmsg	Unbinds a user-defined message from a constraint.
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 SQL Server users and processes or about a particular user or process.

Introduction to System Procedures