This section provides the syntax and brief descriptions for Adaptive Server system stored procedures. See Reference Manual: Procedures for more information.
Displays all active roles.
sp_activeroles [expand_down]
Adds an abstract plan group.
sp_add_qpgroup new_name
Creates a limit on the number of server resources that can be used by an Adaptive Server login and/or an application to execute a query, query batch, or transaction.
sp_add_resource_limit name, appname, rangename, limittype, limitvalue [, enforced [, action [, scope ]]]
Adds a named time range to an Adaptive Server.
sp_add_time_range name, startday, endday, starttime, endtime
Allows an Adaptive Server user to be known in a database as another user.
sp_addalias loginame, name_in_db
Allows users to enter user-defined audit records (comments) into the audit trail.
sp_addauditrecord [text [, db_name [, obj_name [, owner_name [, dbid [, objid]]]]]]
Adds another system audit table after auditing is installed.
sp_addaudittable devname
Adds an engine to an existing engine group or, if the group does not exist, creates an engine group and adds the engine.
sp_addengine engine_number, engine_group
Creates or updates a user-defined execution class that you can bind to client applications, logins, and stored procedures.
sp_addexeclass classname, priority, timeslice, engine_group
Creates an extended stored procedure (ESP) in the master database.
sp_addextendedproc esp_name, dll_name
Component Integration Services only– creates an alternate login account and password to use when communicating with a remote server through Component Integration Services.
sp_addexternlogin server, loginame, externname [, externpasswd] [rolename]
Adds a group to a database. Groups are used as collective names in granting and revoking privileges.
sp_addgroup grpname
Defines the names of the months and days for an alternate language and its date format.
sp_addlanguage language, alias, months, shortmons, days, datefmt, datefirst
Adds a new user account to Adaptive Server; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified login at creation.
sp_addlogin loginame, passwd [, defdb] [, deflanguage] [, fullname] [, passwdexp] [, minpwdlen] [, maxfailedlogins] [, auth_mech]
Adds user-defined messages to sysusermessages for use by stored procedure print and raiserror calls and by sp_bindmsg.
sp_addmessage message_num, message_text [, language [, with_log [, replace]]]
Component Integration Services only – specifies the mapping between a local table and an external storage location.
sp_addobjectdef tablename, objectdef [,"objecttype"]
Authorizes a new remote server user by adding an entry to master.dbo.sysremotelogins.
sp_addremotelogin remoteserver [, loginame [, remotename] ]
Defines a segment on a database device in a database.
sp_addsegment segname, dbname, devname
Defines a remote server, or defines the name of the local server.
sp_addserver lname [, class [, pname]]
Creates a threshold to monitor space on a database segment.
sp_addthreshold dbname, segname, free_space, proc_name
Creates a user-defined datatype.
sp_addtype typename, phystype [(length) | (precision [, scale])] [, "identity" | nulltype]
Adds a dump device to Adaptive Server.
sp_addumpdevice {"tape" | "disk"}, logicalname,
physicalname [, tapesize]
Adds a new user to the current database.
sp_adduser loginame [, name_in_db [, grpname]]
Enables and disables the logging of a system-defined or user-defined message in the Adaptive Server error log.
sp_altermessage message_id, parameter, parameter_value
Allows a System Security Officer to configure auditing options.
sp_audit option, login_name, object_name [,setting]
sp_audit ‘restart’
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_autoconnect server, {true|false}
[, loginame]
Reformats the width of variable-length character data to display only non-blank characters.
sp_autoformat fulltabname [, selectlist , whereclause , orderby ]
Binds a database, table, index, text object, or image object to a data cache.
sp_bindcache cachename, dbname [, [ownername.]tablename [, indexname | "text only"]]
Binds a user-defined default to a column or user-defined datatype.
sp_bindefault defname, objname [, futureonly]
Associates an execution class with a client application, login, or stored procedure.
sp_bindexeclass "object_name", "object_type", "scope", "classname"
Binds a user message to a referential integrity constraint or check constraint.
sp_bindmsg constrname, msgid
Binds a rule to a column or user-defined datatype.
sp_bindrule rulename, objname [, futureonly]
Creates, configures, reconfigures, and drops data caches, and provides information about them.
sp_cacheconfig [cachename [ ,"cache_size[P|K|M|G]" ] [,logonly | mixed ] [,strict | relaxed ] ] [, "cache_partition=[1|2|4|8|16|32|64]"]
Enables or disables prefetching (large I/O) and MRU cache replacement strategy for a table, index, text object, or image object.
sp_cachestrategy dbname, [ownername.]tablename
[, indexname | "text only" | "table only"
[, { prefetch | mru }, { "on" | "off"}]]
Changes the owner of a user database.
sp_changedbowner loginame [, true ]
Changes a user’s group.
sp_changegroup grpname, username
Checks the current database for names that contain characters not in the 7-bit ASCII set.
sp_checknames [help | silent]
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_checkreswords [user_name_param]
Checks for the existence of the source text of the compiled object, and for the existence of computed column source text.
sp_checksource [objname [, tabname [, username]]]
Changes the max_rows_per_page, fillfactor, reservepagegap, or exp_row_size value for future space allocations of a table or an index; sets the concurrency_opt_threshold for a table. Provides the user interface for optimistic index locking.
sp_chgattribute objname, {"max_rows_per_page" | "fillfactor" |
"reservepagegap" | "exp_row_size"
concurrency_opt_threshold | “optimistic_index_lock” | “identity_burn_max”}, value, optvalue
sp_chgattribute objname, {"identity_gap", set_number |
“dealloc_first_txtpg”, value}
Clears the execution attributes of an Adaptive Server session that was set by ssp_setpsexe.
sp_clearpsexe spid, exeattr
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_clearstats [loginame]
Displays the IP (Internet Protocol) address of every Adaptive Server task with an attached client application, including the spid and the client host name.
sp_client addr[“spid”]
Compares all abstract plans in two abstract plan groups.
sp_cmp_all_qplans group1, group2 [, mode]
Compares two abstract plans.
sp_cmp_qplans id1, id2
Defines a common key—columns that are frequently joined—between two tables or views.
sp_commonkey tabaname, tabbname, col1a, col1b [, col2a, col2b, ..., col8a, col8b]
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_companion
[server_name
{, configure
[, {with_proxydb | NULL}]
[, srvlogin]
[, server_password]
[, cluster_login]
[, cluspassword]]
| drop
| suspend
| resume
| prepare_failback
| do_advisory}
{, all
| help
| group attribute_name
| base attribute_name}
Displays configuration parameters by group, their current values, their default values, the value to which they have most recently been set, and the amount of memory used by this setting.
sp_configure [configname [, configvalue] | group_name | non_unique_parameter_fragment]
sp_configure "configuration file", 0, {"write" | "read" | "verify" | "restore"}
"file_name"
Copies all plans for one abstract plan group to another group.
sp_copy_all_qplans src_group, dest_group
Copies one abstract plan to an abstract plan group.
sp_copy_qplan src_id, dest_group
Displays the number of indexes, objects, or databases in Adaptive Server.
sp_countmetadata "configname" [, dbname]
Reports information about a specific cursor or all execute cursors that are active for your session.
sp_cursorinfo [{cursor_level | null}] [, cursor_name]
Allows you to install automatic database expansion procedures on database/segment pairs and devices, define site-specific policies for individual segments and devices, and simulate execution of the database expansion machinery, to study the operation before engaging large volume loads.
sp_dbextend ’help’[, <command > ]
sp_dbextend [ ['set', ['threshold', dbname, segmentname, freespace |
'database', dbname, segmentname {[ [, growby ] [, maxsize ] ]} |
'device', devicename { [ [, growby ] [, maxsize ] ] }] |
'clear', 'threshold', dbname, segmentname
sp_dbextend 'clear', 'database' [, dbname [, segmentname ] ]
sp_dbextend 'clear', 'device' [, devicename ]
sp_dbextend 'modify', 'database', dbname, segmentname,
{ 'growby' | 'maxsize' }, newvalue
sp_dbextend 'modify', 'device', devicename, { ’growby’ | ' maxsize ' },
newvalue
sp_dbextend { 'list' | 'listfull' } [, 'database' [, dbname [, segmentname
[, order_by_clause ] ] ] ]
sp_dbextend { 'list' | 'listfull' } [, 'device' [, devicename [, order_by_clause ] ] ]
sp_dbextend { 'list' | 'listfull' }, [ 'threshold' [ , @dbname
[ , @segmentname ] ] ]
sp_dbextend 'check', 'database' [, dbname [, segmentname ] ]
sp_dbextend { 'simulate' | 'execute' }, dbname, segmentname [, iterations ]
sp_dbextend 'trace', {'on' | 'off' }
sp_dbextend 'reload [defaults]'
sp_dbextend { 'enable' | 'disable' }, 'database' [, dbname [, segmentname ] ]
sp_dbextend 'who' [, '<spid>' | 'block' | 'all' ]
Displays or changes database options, and enables the asynchronous log service feature.
sp_dboption [dbname, optname, optvalue [, dockpt]]
Specifies the order in which user databases are recovered and lists the user-defined recovery order of a database or all databases.
sp_dbrecovery_order [database_name [, rec_order [, force]]]
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_dbremap dbname
Component Integration Services only – defines a default storage location for objects in a local database.
sp_defaultloc dbname, defaultloc, defaulttype
Displays information about database object dependencies.
sp_depends objname[, column_name]
UNIX platforms only – changes the device parameter settings of an existing database device file.
sp_deviceattr logicalname, optname, optvalue
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_diskdefault logicalname, {defaulton | defaultoff}
Displays the status of audit options.
sp_displayaudit ["procedure" | "object" | "login" | "database" | "global" | "default_object" | "default_procedure" [, "name"]]
Sets or shows which Adaptive Server configuration parameters appear in sp_configure output.
sp_displaylevel [loginame [, level]]
Displays information about a login account. Also displays information about the hierarchy tree above or below the login account when you so specify.
sp_displaylogin [loginame [, expand_up | expand_down]]
Displays all roles granted to another role, or displays the entire hierarchy tree of roles in table format.
sp_displayroles [grantee_name [, mode]]
Removes the alias user name identity established with sp_addalias.
sp_dropalias loginame [, force]
Deletes all abstract plans in an abstract plan group.
sp_drop_all_qplans name
Drops an abstract plan group.
sp_drop_qpgroup group
Drops an abstract plan.
sp_drop_qplan id
Removes one or more resource limits from Adaptive Server.
sp_drop_resource_limit { name, appname }
[, rangename, limittype, enforced, action, scope]
Removes a user-defined time range from Adaptive Server.
sp_drop_time_range name
Drops an Adaptive Server database device or dump device.
sp_dropdevice logicalname
Drops an engine from a specified engine group or, if the engine is the last one in the group, drops the engine group.
sp_dropengine engine_number, engine_group
Drops a user-defined execution class.
sp_dropexeclass classname
Removes an extended stored procedure (ESP).
sp_dropextendedproc esp_name
Component Integration Services only – Drops the definition of a remote login previously defined by sp_addexternlogin.
sp_dropexternlogin server [, loginame [, rolename ] ]
Removes lock promotion values from a table or database.
sp_dropglockpromote {"database" | "table"}, objname
Drops a group from a database.
sp_dropgroup grpname
Removes from the syskeys table a key that had been defined using sp_primarykey, sp_foreignkey, or sp_commonkey.
sp_dropkey keytype, tabname [, deptabname]
Drops an alternate language from the server and removes its row from master.dbo.syslanguages.
sp_droplanguage language [, dropmessages]
Drops an Adaptive Server user login by deleting the user’s entry from master.dbo.syslogins.
sp_droplogin loginame
Drops user-defined messages from sysusermessages.
sp_dropmessage message_num [, language]
Component Integration Services only – deletes the external storage mapping provided for a local object.
sp_dropobjectdef tablename
Drops a remote user login.
sp_dropremotelogin remoteserver [, loginame [, remotename] ]
Removes row lock promotion threshold values from a database or table.
sp_droprowlockpromote {"database" | "table"}, objname
Drops a segment from a database or unmaps a segment from a particular database device.
sp_dropsegment segname, dbname [, device]
Drops a server from the list of known servers or drops remote logins and external logins in the same operation.
sp_dropserver server [, droplogins]
Removes a free-space threshold from a segment.
sp_dropthreshold dbname, segname, free_space
Drops a user-defined datatype.
sp_droptype typename
Drops a user from the current database.
sp_dropuser name_in_db
Specifies the amount of data dumped by Backup Server during the dump database operation.
sp_dumpoptimize [ ’archive_space =
{maximum | minimum | default }’ ]
sp_dumpoptimize [ ’reserved_threshold =
{nnn | default }’ ]
sp_dumpoptimize [ ’allocation_threshold =
{nnn | default }’ ]
Enables you to bring an engine online or offline.
sp_engine {“online” | [offline | can_offline] [, engine_id] |
[“shutdown”, engine_id]}
Estimates the amount of space required for a table and its indexes, and the time needed to create the index.
sp_estspace table_name, no_of_rows, fill_factor, cols_to_max, textbin_len, iosec, page_size
Exports all plans for a specified user and abstract plan group to a user table.
sp_export_qpgroup usr, group, tab
Extends the range of a segment to another database device.
sp_extendsegment segname, dbname, devname
Starts and stops EJB Server. Displays status information about EJB Server.
sp_extengine 'ejb_server', '{ start | stop | status }'
Reports information about all the locks held by a family (coordinating process and its worker processes) executing a statement in parallel.
sp_familylock [fpid1 [, fpid2]]
Finds an abstract plan, given a pattern from the query text or plan text.
sp_find_qplan pattern [, group ]
Repairs the index on one of your system tables when it has been corrupted.
sp_fixindex dbname, tabname, indid
Flushes statistics from in-memory storage to the systabstats and sysstatistics system tables.
sp_flushstats objname
Provides access to all the pages in a database that were previously marked suspect by recovery.
sp_forceonline_db dbname,
{"sa_on" | "sa_off" | "all_users"}
Provides access to an index previously marked suspect by recovery.
sp_forceonline_object dbname, objname, indid,
{sa_on | sa_off | all_users} [, no_print]
Provides access to pages previously marked suspect by recovery.
sp_forceonline_page dbname, pgid,
{"sa_on" | "sa_off" | "all_users"}
Defines a foreign key on a table or view in the current database.
sp_foreignkey tabname, pktabname, col1 [, col2] ... [, col8]
Unloads a dynamic link library (DLL) that was previously loaded into XP Server memory to support the execution of an extended stored procedure (ESP).
sp_freedll dll_name
Retrieves stored message strings from sysmessages and sysusermessages for print and raiserror statements.
sp_getmessage message_num, result output [, language]
Windows NT only – assigns Adaptive Server roles or default permissions to Windows NT users and groups when Integrated Security mode or Mixed mode (with Named Pipes) is active.
sp_grantlogin {login_name | group_name}
["role_list" | default]
Performs administrative tasks on Adaptive Servers configured with Sybase Failover in a high availability system.
sp_ha_admnin [cleansessions | help]
Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes, as well as computed columns and function-based indexes.
sp_help [objname]
Reports on resource limits.
sp_help_resource_limit [name [, appname [, limittime [, limitday [, scope [, action[, verbose]]]]]]]
Reports information on an abstract plan group.
sp_help_qpgroup [ group [, mode ]]
Reports information about an abstract plan.
sp_help_qplan id [, mode ]
Lists partition-related information of a table or index.
sp_helpartition [ tabname [, { null | indexname | ‘all’ }[, partitionname ] ] ]
Displays information about the objects that are bound to a data cache or the amount of overhead required for a specified cache size.
sp_helpcache {cache_name | "cache_size[P|K|M|G]"}
Reports information on the computed columns in a specified table.
sp_helpcomputedcolumn {tabname}
Reports help information on configuration parameters.
sp_helpconfig "configname", ["size"]
Reports information about integrity constraints used in the specified tables.
sp_helpconstraint [objname] [, detail]
Reports information about a particular database or about all databases.
sp_helpdb [ dbname [, order ] ]
Reports information about a particular device or about all Adaptive Server database devices and dump devices.
sp_helpdevice [devname]
Displays extended stored procedures (ESPs) in the current database, along with their associated DLL files.
sp_helpextendedproc [esp_name]
Component Integration Services only – reports information about external login names.
sp_helpexternlogin [ server [, loginame [, rolename ] ] ]
Reports information about a particular group or about all groups in the current database.
sp_helpgroup [grpname]
Reports information about the indexes created on a table, and on computed column indexes and function-based indexes.
sp_helpindex objname
Displays information about Java classes and associated JARs that are installed in the database.
sp_helpjava ["class" [, java_class_name [, “detail” | “depends” ] ] | "jar" [, jar_name [, “depends” ] ] ]
Lists the columns in two tables or views that are likely join candidates.
sp_helpjoins lefttab, righttab
Reports information about a primary, foreign, or common key of a particular table or view, or about all keys in the current database.
sp_helpkey [tabname]
Reports information about a particular alternate language or about all languages.
sp_helplanguage [language]
Reports the name of the device that contains the first page of the transaction log.
sp_helplog
Component Integration Services only – reports owners, objects, and type information for remote object definitions.
sp_helpobjectdef [objname]
Reports information about a particular remote server’s logins or about all remote server logins.
sp_helpremotelogin [remoteserver [, remotename]]
Reports on permissions for database objects, users, groups, or roles.
sp_helprotect [name [, username [, "grant" [,"none"|"granted"|"enabled"|role_name]]]]
Reports information about a particular segment or about all segments in the current database.
sp_helpsegment [segname]
Reports information about a particular remote server or about all remote servers.
sp_helpserver [server]
Displays Adaptive Server’s default sort order and character set.
sp_helpsort
Displays the source text of a compiled object. Displays the source text of computed columns or function-based index definitions.
sp_helptext objname [,number]
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_helpthreshold [segname]
Reports information about a particular user, group, or alias, or about all users, in the current database.
sp_helpuser [name_in_db]
Hides the source text for the specified compiled object. Hides the text of computed columns and function-based index keys.
sp_hidetext [objname [, tabname [, username]]]
Imports abstract plans from a user table into an abstract plan group.
sp_import_qpgroup tab, usr, group
Checks user tables for indexes marked as suspect during recovery following a sort order change.
sp_indsuspect [tab_name]
Creates an LDAP URL search string, lists an LDAP URL search string, or verifies an LDAP URL search string or login.
sp_ldapadmin { set_primary_url, ‘ldapurl’ |
set_secondary_url, { ‘ldapurl’ | null } |
set_access_acct, account_distinguished_name, account_password |
set_dn_lookup_url, distinguished_name_url |
list_urls | check_url, ‘ldapurl’ |
check_login, ‘login_name’ }
Dynamically starts and stops listeners on Adaptive Server on any given port on a per-engine basis.
sp_listener “command”, “server_name”, engine | remaining
sp_listener “command”, “[protocol:]machine:port”, engine
Lists all databases that currently have offline pages because of corruption detected on recovery.
sp_listsuspect_db
Lists all indexes in a database that are currently offline because of corruption detected on recovery.
sp_listsuspect_object [dbname]
Lists all pages in a database that are currently offline because of corruption detected on recovery.
sp_listsuspect_page [dbname]
Configures license management-related information on Adaptive Server.
sp_lmconfig “edition”, edition_type, “license type”, license_type_name, “smtp host”, smtp_host_name, “smtp port”, smtp_port_number, “email sender”, sender_email_address, “email recipients”, email_recipients, “email severity”, email_severity
Reports information about processes that currently hold locks.
sp_lock [spid1 [, spid2]]
Locks an Adaptive Server account so that the user cannot log in, or displays a list of all locked accounts.
sp_locklogin login | all | NULL | wildcard_string , "lock" |"unlock", [ except_login_name | except_role_name ]
sp_locklogin
Moves the transaction log of a database with log and data on the same device to a separate database device.
sp_logdevice dbname, devname
Windows NT only – displays the value of one or all integrated security parameters.
sp_loginconfig ["parameter_name"]
Windows NT only – displays all roles granted to Windows NT users and groups with sp_grantlogin.
sp_logininfo ["login_name" | "group_name"]
Changes the log I/O size used by Adaptive Server to a different memory pool when doing I/O for the transaction log of the current database.
sp_logiosize ["default" | "size" | "all"]
Maps external users to Adaptive Server logins.
sp_maplogin (authentication_mech | null), (client_username | null), (action | login_name | null)
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_metrics [‘backup’ backup_group_ID | ‘drop’, ‘gid’ [, ‘id’] | ‘flush’ | ‘help’, ‘command’]
Changes a resource limit by specifying a new limit value, or the action to take when the limit is exceeded, or both.
sp_modify_resource_limit {name, appname }
rangename , limittype , limitvalue , enforced , action , scope
Changes the start day, start time, end day, and/or end time associated with a named time range.
sp_modify_time_range name, startday, endday, starttime, endtime
Modifies the default database, default language, default role activation, login script, full name, the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified Adaptive Server login account.
sp_modifylogin {loginame | “all overrides”}, option, value
Allows the System Administrator to modify the density values of a column—or columns—in sysstatistics
sp_modifystats [database].[owner].table_name,
{“column_group” | “all”},
MODIFY_DENSITY,
{range | total},
{absolute | factor},
“value”
sp_modifystats [database].[owner].table_name, column_name, REMOVE_SKEW_FROM_DENSITY
Modifies a threshold by associating it with a different threshold procedure, free-space level, or segment name.
sp_modifythreshold dbname, segname, free_space [, new_proc_name] [, new_free_space] [, new_segname]
Displays statistics about Adaptive Server.
sp_monitor [ connection, [cpu | diskio | elapsed time ] ] [event, [spid ] ] [procedure, [ dbname, [ procname, [, summary | detail ] ] ] ] [ enable ] [ disable ] [ statement, [ cpu | diskio | elapsed time ] ] [ help], [ connection | statement | procedure | event ] ]
Displays cache usage statistics regarding metadata descriptors for indexes, objects, and databases, and reports statistics on auxiliary scan descriptors used for referential integrity queries and usage statistics for transaction descriptors and DTX participants.
sp_monitorconfig "configname" [ , “result_tbl_name”] [, “full”]
Shows lock contention, lock wait-time, and deadlock statistics for tables and indexes.
sp_object_stats interval [, top_n [, dbname, objname [, rpt_option ]]]
Component Integration Services only – allows the user to pass a SQL command buffer to a remote server.
sp_passthru server, command, errcode, errmsg, rowcount [, arg1, arg2, ... argn]
Adds or changes a password for an Adaptive Server login account.
sp_password caller_passwd, new_passwd [, loginame, immediate]
Puts future space allocations for a table or index on a particular segment.
sp_placeobject segname, objname
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_plan_dbccdb [dbname]
Creates, drops, resizes, and provides information about memory pools within data caches.
sp_poolconfig cache_name [, "mem_size [P|K|M|G]", "config_poolK" [, "affected_poolK"]]
sp_poolconfig cache_name, "io_size ", "wash=size[P|K|M|G]"
sp_poolconfig cache_name, "io_size ", "local async prefetch limit=percent "
Checks and rebuilds indexes after a cross-platform load database where the endian types are different.
sp_post_xpload
Defines a primary key on a table or view.
sp_primarykey tabname, col1 [, col2, col3, ..., col8]
Windows NT only – reads, processes, sends, and deletes messages in the Adaptive Server message inbox, using the sp_findnextmsg, xp_readmail, xp_sendmail, and xp_deletemail system extended stored procedures (ESPs).
sp_processmail [subject] [, originator [, dbuser [, dbname [, filetype [, separator]]]]]
Displays or changes the execution modes associated with stored procedures.
sp_procxmode [procname [, tranmode]]
Causes each stored procedure and trigger that uses the named table to be recompiled the next time it runs.
sp_recompile objname
Remaps a stored procedure, trigger, rule, default, or view from releases later than 4.8 and prior to 10.0 to be compatible with releases 10.0 and later. Use sp_remap on pre-existing objects that the upgrade procedure failed to remap.
sp_remap objname
Displays or changes remote login options.
sp_remoteoption [remoteserver [, loginame [, remotename [, optname [, optvalue]]]]]
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_remotesql server, query[, query2, ... , query254]
Changes the name of a user-created object or user-defined datatype in the current database.
sp_rename objname, newname [,“index” | “column”]
Renames an abstract plan group.
sp_rename_qpgroup old_name, new_name
Changes the name of a user database.
sp_renamedb dbname, newname
Reports statistics on system usage.
sp_reportstats [loginame]
Windows NT only – revokes Adaptive Server roles and default permissions from Windows NT users and groups when Integrated Security mode or Mixed mode (with Named Pipes) is active.
sp_revokelogin {login_name | group_name}
Grants or revokes roles to an Adaptive Server login account.
sp_role {"grant" | "revoke"}, rolename, loginame
Sends a message to a User Datagram Protocol (UDP) port.
sp_sendmsg ip_address, port_number, message
Displays or changes remote server options.
sp_serveroption [server, optname, optvalue]
Changes the text of the abstract plan of an existing plan without changing the associated query.
sp_set_qplan id, plan
Assigns or changes the alias for an alternate language.
sp_setlangalias language, alias
Sets or changes the lock promotion thresholds for a database, for a table, or for Adaptive Server.
sp_setpglockpromote {"database" | "table"}, objname, new_lwm,
new_hwm, new_pct
sp_setpglockpromote server, NULL, new_lwm, new_hwm, new_pct
Sets custom execution attributes for a session while the session is active.
sp_setpsexe spid, exeattr, value
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_setrowlockpromote "server", NULL, new_lwm, new_hwm, new_pct
sp_setrowlockpromote {"database" | "table"}, objname, new_lwm,
new_hwm, new_pct
Displays or sets the recovery fault isolation mode for a user database, which governs how recovery behaves when it detects data corruption.
sp_setsuspect_granularity [dbname [, "database" | "page" [, "read_only"]]]
Displays or sets the maximum number of suspect pages that Adaptive Server allows in a database before marking the entire database suspect.
sp_setsuspect_threshold [dbname [, threshold]]
Displays information about engine group assignments, bound client applications, logins, and stored procedures.
sp_showcontrolinfo [object_type, object_name, spid ]
Displays the execution class attributes and the engines in any engine group associated with the specified execution class.
sp_showexeclass [execlassname]
Displays the showplan output for any user connection for the current SQL statement or for a previous statement in the same batch.
sp_showplan spid, batch_id output, context_id output, stmt_num output
Displays execution class, current priority, and affinity for all client sessions running on Adaptive Server.
sp_showpsexe [spid]
Displays estimates of the number of rows, the number of data pages, the size of indexes, and the space used by a specified table or by all tables in the current database.
sp_spaceused [objname [,1] ]
Adds, deletes, or displays a list of server certificates for Adaptive Server.
sp_ssladmin {[addcert, certificate_path [, password|NULL]]
[dropcert, certificate_path]
[lscert]
[help]}
[lsciphers]
[setciphers, {"FIPS" | "Strong" | "Weak" | "All"
| quoted_list_of_ciphersuites}]
Displays the syntax of Transact-SQL statements, system procedures, utilities, and other routines for Adaptive Server, depending on which products and corresponding sp_syntax scripts exist on your server.
sp_syntax word [, mod][, language]
Displays performance information.
sp_sysmon begin_sample
sp_sysmon interval [, noclear,[,section [, applmon]]]
sp_sysmon { end_sample | interval } [, section [, applmon] ]
sp_sysmon { end_sample | interval } [, applmon ]
Allows users to create the default temporary database group, bind temporary databases to the default temporary database group, and bind users and applications to the default temporary database group or to specific temporary databases
sp_tempdb [
[ { “create” | “drop” } , “groupname” ] |
[ { “add” | “remove” } , “tempdbname”, “groupname” ] |
[ { “bind”, ““objtype”, “objname”, “bindtype”, “bindobj”
[, “scope”, “hardness” ] } |
{ “unbind”, “objtype”, “objname” [, “scope” ] } ] |
[ “unbindall_db”, “tempdbname” ] |
[ show [, "all" | "gr" | "db" | "login" | "app" [, “name” ] ] |
[ who, “dbname” ]
[ help ] ]
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_thresholdaction @dbname, @segment_name, @space_left, @status
Reports information about active transactions.
sp_tranactions ["xid", xid_value] |
["state", {"heuristic_commit" | "heuristic_abort"
| "prepared" | "indoubt"} [, "xactname"]] |
["gtrid", gtrid_value]
Unbinds a database, table, index, text object, or image object from a data cache.
sp_unbindcache dbname [,[owner.]tablename [, indexname | "text only"]]
Unbinds all objects that are bound to a cache.
sp_unbindcache_all cache_name
Unbinds a created default value from a column or from a user-defined datatype.
sp_unbindefault objname [, futureonly]
Removes the execution class attribute previously associated with an client application, login, or stored procedure for the specified scope.
sp_unbindexeclass object_name, object_type, scope
Unbinds a user-defined message from a constraint.
sp_unbindmsg constrname
Unbinds a rule from a column or from a user-defined datatype.
sp_unbindrule objname [, futureonly [, “accessrule” | “all”]]
Returns the version information of the installation scripts that was last run and whether it was successful.
sp_version [script_file, [all]]
Notifies the Backup Server that the operator performed the requested volume handling during a dump or load.
sp_volchanged session_id, devname, action [, fname [, vname]]
Reports information about all current Adaptive Server users and processes or about a particular user or process.
sp_who [loginame | "spid"]
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
