Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 14 Using Stored Procedures

System procedures

Types of system procedures

System procedures can be grouped by function, such as auditing, security administration, data definition, and so on. The following sections list the types of system procedures.

For complete information about the system procedures, see the Reference Manual.

System procedures for auditing

These system procedures are used for:

Controlling audit settings
Creating and managing the tables which hold the audit queue

The procedures in this category are:

sp_addauditrecord

sp_addaudittable

sp_audit

sp_displayaudit

For more information on auditing, see the Security Administration Guide.

System procedures used for security administration

These system procedures are used for:

Adding, dropping, and reporting on logins on Adaptive Server
Adding, dropping, and reporting on users, groups, roles, and aliases in a database
Changing passwords and default databases
Adding, dropping, and reporting on remote servers that can access the current Adaptive Server
Adding the names of users from remote servers who can access the current Adaptive Server

The procedures in this category are:

sp_activeroles	sp_changegroup	sp_droplogin	sp_locklogin
sp_addalias	sp_displaylogin	sp_dropuser	sp_modifylogin
sp_addgroup	sp_displayroles	sp_helpgroup	sp_password
sp_addlogin	sp_dropalias	sp_helprotect	sp_role
sp_adduser	sp_dropgroup	sp_helpuser

Additional system procedures are available for Windows NT. The procedures in this category are:

sp_grantlogin	sp_logininfo	sp_revokelogin
sp_loginconfig	sp_processmail

System procedures used for remote servers

These system procedures are used for:

Adding, dropping and reporting on remote servers that can access the current Adaptive Server
Adding the names of users from remote servers who can access the current Adaptive Server

The procedures in this category are:

sp_addremotelogin	sp_dropserver	sp_remoteoption
sp_addserver	sp_helpremotelogin	sp_serveroption
sp_dropremotelogin	sp_helpserver

Additional system procedures are available if you have Component Integration Services installed. The procedures in this category are:

sp_addexternlogin	sp_dropexternlogin	sp_passthru
sp_addobjectdef	sp_dropobjectdef	sp_remotesql
sp_autoconnect	sp_helpexternlogin
sp_defaultloc	sp_helpobjectdef

System procedures for managing databases

These system procedures are used for:

Changing the owner of a database
Displaying and changing database options

The procedures in this category are:

sp_changedbowner	sp_dbremap	sp_renamedb
sp_dboption	sp_helpdb

System procedures used for data definition and database objects

These system procedures are used for:

Binding and unbinding rules and defaults
Adding, dropping, and reporting on primary keys, foreign keys, and common keys
Adding, dropping, and reporting on user-defined datatypes
Renaming database objects and user-defined datatypes
Re-optimizing stored procedures and triggers
Binding and unbinding objects to data caches
Reporting on database objects, user-defined datatypes, dependencies among database objects, databases, indexes, and space used by tables and indexes
Getting help on command syntax

The procedures in this category are

sp_addextendedproc	sp_dropkey	sp_helptext
sp_addtype	sp_droptype	sp_hidetext
sp_bindcache	sp_estspace	sp_poolconfig
sp_bindefault	sp_foreignkey	sp_primarykey
sp_bindrule	sp_freedll	sp_procxmode
sp_cacheconfig	sp_help	sp_recompile
sp_cachestrategy	sp_helpartition	sp_rename
sp_chgattribute	sp_helpcache	sp_spaceused
sp_checksource	sp_helpconstraint	sp_unbindcache
sp_commonkey	sp_helpextendedproc	sp_unbindcache_all
sp_cursorinfo	sp_helpindex	sp_unbindefault
sp_depends	sp_helpjoins	sp_unbindrule
sp_dropextendedproc	sp_helpkey

System procedures used for user-defined messages

These system procedures are used for:

Adding user-defined messages to the sysusermessages table in a user database
Dropping user-defined messages from sysusermessages
Retrieving messages from either sysusermessages or sysmessages in the master database for use in print and raiserror statements

The procedures in this category are:

sp_addmessage	sp_bindmsg	sp_getmessage
sp_altermessage	sp_dropmessage	sp_unbindmsg

System procedures for languages

These system procedures are used for:

Managing character sets
Adding and dropping alternate languages

The procedures in this category are:

sp_addlanguage	sp_helplanguage	sp_setlangalias
sp_checknames	sp_helpsort
sp_droplanguage	sp_indsuspect

System procedures used for device management

These system procedures are used for:

Adding, dropping, and reporting on database and dump devices
Adding, dropping, and extending database segments

The procedures in this category are:

sp_addsegment	sp_dropsegment	sp_helpsegment
sp_addumpdevice	sp_extendsegment	sp_logdevice
sp_diskdefault	sp_helpdevice	sp_logiosize
sp_dropdevice	sp_helplog	sp_placeobject

System procedures used for backup and recovery

These system procedures are used for:

Creating and managing thresholds
Displaying and setting recovery isolation mode
Communicating with Backup Server(TM)

The procedures in this category are:

sp_addthreshold	sp_helpthreshold	sp_setsuspect_granularity
sp_dropthreshold	sp_listsuspect_db	sp_setsuspect_threshold
sp_forceonline_db	sp_listsuspect_page	sp_thresholdaction
sp_forceonline_page	sp_modifythreshold	sp_volchanged

System procedures used for configuration and tuning

These system procedures are used for:

Changing and reporting on configuration parameters
Reporting on system usage
Reporting performance behavior
Monitoring Adaptive Server activity

The procedures in this category are:

sp_clearstats	sp_monitor
sp_configure	sp_monitorconfig
sp_countmetadata	sp_reportstats
sp_displaylevel	sp_showplan
sp_helpconfig	sp_sysmon

System procedures used for system administration

These system procedures are used for:

Reporting on locks and the users currently running processes
Altering the lock promotion thresholds of a table or database
Creating and reporting on resource limits and time ranges
Creating and reporting on execution classes and engine groups
Planning the layout of the dbccdb database

The procedures in this category are:

sp_addengine	sp_drop_resource_limit	sp_setpsexe
sp_addexeclass	sp_drop_time_range	sp_showcontrolinfo
sp_add_resource_limit	sp_familylock	sp_showexeclass
sp_add_time_range	sp_help_resource_limit	sp_showpsexe
sp_bindexeclass	sp_lock	sp_unbindexeclass
sp_clearpsexe	sp_modify_resource_limit	sp_setpsexe
sp_dropexeclass	sp_modify_time_range	sp_who
sp_dropengine	sp_setpglockpromote
sp_dropglockpromote	sp_plan_dbccdb

See the System Administration Guide for more information about the system procedures that accomplish these administrative tasks.

System procedures for upgrade

These system procedures are used for:

Checking for reserved words
Remapping objects
Checking the query processing mode of an object

The procedures in this category are:

sp_checkreswords

sp_remap

sp_procqmode


	Expand Search