System procedures

These are the syntax and very brief descriptions for Adaptive Server system stored procedures. See Reference Manual: Procedures for complete 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 a 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 [, instance_id]

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 
	[, instance_id]

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 CIS.

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]]

sp_addserver 'logical_server_name', ASEnterprise, 'host:port:filter'

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]

• Or: 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]

Produces readable result set data, reformatting 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, or image 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 | inmemory_storage][, strict | relaxed]]
	[, "cache_partition=[1 | 2 | 4 | 8 | 16 | 32 | 64]"]
	[, instance instance_name]

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.

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” | "plldegree"}
	, value,  optvalue

sp_chgattribute objname,
	{"identity_gap", set_number | “dealloc_first_txtpg”, value}

Allows you to define when and how to remove login and password-related attributes stored in user-defined tables.

sp_cleanpwdchecks, login_name

Clears the execution attributes of an Adaptive Server session that was set by sp_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 address of every Adaptive Server task with an attached client application, including the spid and the client host name.

sp_client addr [spid]

In cluster environments – reports on the free, used, and retained locks in the cluster.

sp_clusterlockusage

Migrates a connection to a different logical cluster or instance:

sp_cluster connection, migrate, lc_name, instance_name, “spid_list”

Determines if previous connection migrations to a new instance are pending, and terminates the migrations if they are:

sp_cluster connection, [‘migrate_status’ | 'migrate_cancel' ][, ‘spid_list’]

Modifies an outstanding action, such as canceling the action or changing the timing of the action:

sp_cluster logical, "action", lc_name, { 
	cancel, action_handle |
	modify_time, action_handle, wait_option[, timeout ] |
	release, action_handle }

Adds a resource or one or more routes to the logical cluster:

sp_cluster logical, "add", lc_name, {
	 route, route_type, key_list |
  	 instance, instance_list |
 	 failover, instance_list }

Moves a route from one logical cluster to another:

sp_cluster logical, “alter”, lc_name, route, route_type, key_list

Creates a new logical cluster:

sp_cluster logical, "create", lc_name

Stops the logical cluster on one or more instances or the entire logical cluster, and places the instances or the cluster in the inactive state:

sp_cluster logical, "deactivate", lc_name, { 
	"cluster" |
	"instance", instance_list }
   [, wait_option[, timeout,[, @handle output ]]]

Drops a logical cluster, or one or more resources from the logical cluster:

sp_cluster logical, "drop",  lc_name, 
	{cluster | instance, instance_list |
	 failover, instance_list |
	 route, route_type, key_list }

Reverses a manual failover, reinstating the original base instances:

sp_cluster logical, “failback”, lc_name, {
	cluster[, wait_option[, timeout[, @handle output ]]] |
	instance, from_instance_list, to_instance_list[, wait_option[,
		timeout[, @handle output ]]] }

Initiates a manual failover from base instances to failover instances.

sp_cluster logical, “failover”, lc_name, {cluster
	[, to_instance_list[, wait_option[, timeout[, @handle output ]]] 
	| instance, from_instance_list, to_instance_list[, wait_option[,
		timeout[, @handle output ]]] }

Manually gathers and migrates a group of connections to a different logical cluster:

sp_cluster logical, 'gather', lc_name

Displays complete syntax for sp_cluster logical:

sp_cluster logical, “help”

Stops the logical cluster on one or more instances or the entire logical cluster:

sp_cluster logical, "offline", lc_name, 
	{cluster | instance, instance_list }
	[, wait_option[, timeout,[, @handle output ]]]

Starts the default logical cluster on one or more instances:

sp_cluster logical, "online", { lc_name[, instance_list]}

Sets logical cluster rules: the open logical cluster, the failover mode, the system view, the start-up mode, and the load profile:

sp_cluster logical, "set", lc_name, { open 
		| failover, failover_mode 
		| system_view, view_mode 
		| startup, { automatic | manual } 
		| load_profile, profile_name }
	login_distribution, { affinity | "round-robin" }

Displays information about a logical cluster:

sp_cluster logical, "show"
[, lc_name[, {action[, state] | route[, type[, key]]}]]

Lets you set up and manage the load profile for the logical cluster:

sp_cluster profile, [ "show" [, profile_name ]
	| "create", profile_name 
	| "drop", profile_name 
	| "set", profile_name [, weight [, wt_metric [, wt_value ] 
	| threshold [, thr_metric [, thr_value ] ] ]

Lets you set up and manage the load profile for the logical cluster:

sp_cluster profile, [ "show" [, profile_name ] | "create", profile_name | "drop", profile_name | "set", profile_name [, weight [, wt_metric [, wt_value ] | threshold [, thr_metric [, thr_value ] ] ]

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 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}

Verifies whether full compatibility mode can be used.

sp_compatmode

Displays configuration parameters by group, their current values, their non-default value settings, the value to which they have most recently been set, and the amount of memory used by this setting. Displays only the parameters whose display level is the same as or below that of the user.

sp_configure [configname [, configvalue] | group_name | 
	non_unique_parameter_fragment] 'drop instance' 
	[, instance_name] [display_nondefault_settings]

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 imulate 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 [ relax | strict ]]]]

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

Deletes specified backup objects from the IBM Tivoli Storage Manager (TSM).

sp_deletesmob “syb_tsm“, “server_name"{, “database_name”, “object_type”, “dump_type", “until_time", “bs_name"}

Displays information about database object dependencies in the database that depend on a specified table or view, and the tables and views in the database on which the specified view, trigger, or procedure depends. Also displays information about table column dependencies defined in either the column specified or on all the columns in the table.

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.

sp_displaylogin ['user_id' | '[loginame | wildcard]'

Displays all roles granted to another role, or displays the entire hierarchy tree of roles in table format.

sp_displayroles [grantee_name [, mode]]

Validates readiness for downgrade to an earlier 15.0.x release and downgrades the system catalog changes Adaptive Server 15.0.2 modified.

sp_downgrade @cmd = {'prepare' | 'downgrade' | 'help',}
	@toversion = 'n'[, @verbose = 0 | 1][, @override = 0 | 1]

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] [,instance_id]

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 }’ ]

Reports encryption information.

sp_encryption help | helpkey

sp_encryption help | helpkey [, key_name | wildcard]
		[, all_dbs | key_copy | display_cols]

sp_encryption help | helpkey [, system_encr_passwd] 
		[, display_keys]

sp_encryption helpcol [, table_name | column_name ]

sp_encryption helpuser [, user_name | wildcard ][, key_copy]

sp_encryption system_encr_passwd, 'newpasswd' [,'oldpasswd']

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 }'

Contains user-defined logic for password complexity checks.

sp_extrapwdchecks caller_password, new_password, login_name

Reports information about all the locks held by a family executing a statement in parallel.

sp_familylock [fpid1 [, fpid2]]

Finds an abstract plan, given a pattern from the query or plan text.

sp_find_qplan pattern [, group ]

Repairs a set of indexes on a systemt able when it has been corrupted.

sp_fixindex database_name, table_name [, index_id | null] 
	[, index_name | null]  [, force_option]

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 DLL that was previously loaded into XP Server memory to support the execution of an extended stored procedure.

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 only – assigns Adaptive Server roles or default permissions to Windows 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 in a high availability system.

sp_ha_admnin [cleansessions | help]

Reports information about a database object and about system or user-defined datatypes, as well as user-defined functions, 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 ]

Determine which sessions Adaptive Server is tracing.

sp_helpapptrace

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]" ,
	‘instance instance_name’}

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 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[, permission_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, as well as the text for user-defined functions, computed columns, or function-based index definitions.

sp_helptext objname[,grouping_num][, numlines[, printopts]]]

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, as well as 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]

Manages the Java PCA/JVM. Enables or disables arguments and directives, changes configuration values, and reports configuration values.

sp_jreconfig {
	add array_arg, new_string |
	array_clear array_arg |
	array_enable array_arg |
	array_disable array_arg |
	delete array_arg, string_value |
	disable { directive | argument | array_arg, string_value } | 
	enable { directive | argument | array_arg, string_value } |
	list { list_type [, formatted ] | units | units, units_type[, formatted ] } |
	reload_config |
	report { directive[, formatted ] | directive, args[, formatted ]
		|argument[, formatted ] } |
	update { argument, old_value, new_value } }

Creates or lists an LDAP URL search string, verifies an LDAP URL search string or login, or specifies the access accounts and tunable LDAPUA-related parameters.

sp_ldapadmin command [, option1 [, option2]]

Valid command [, option1 [, option2]] options are:	'set_primary_url', 'url'
	'set_secondary_url', 'url'
	'set_dn_lookup_url', 'url'
	'set_secondary_dn_lookup_url', 'url'
	'set_access_acct', 'distinguished_name', 'password'
	'set_secondary_access_acct', 'distinguished_name', 'password'
	'set_failback_interval', time_in_minutes
	'suspend', {'primary' | 'secondary'}
	'activate', {'primary' | 'secondary'} 
	'list'
	'list_urls'
	'list_access_acct'
	'check_url', 'url'
'	'reinit_descriptors'
	'check_login', 'name'
	'set_timeout', timeout_in_milli_seconds
	'set_log_interval', log_interval_in_minutes
	'set_num_retries', num_retries
	'set_max_ldapua_native_threads', max_ldapua_native_threads
	'set_max_ldapua_desc', max_ldapua_desc
	'set_abandon_ldapua_when_full', {true|false}
	'starttls_on_primary', {true|false}
	'starttls_on_secondary', {true|false}
	'help’

Dynamically starts and stops listeners on Adaptive Server on any given port on a per-engine basis.

sp_listener “command”, “server_name | network”, engine | remaining

Or:

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 the object names and IDs of processes that currently hold locks.

sp_lock [spid1[, spid2]] | [@verbose = int]

Locks an Adaptive Server account so that the user cannot log in, or displays a list of all locked accounts.

sp_locklogin login | NULL | wildcard_string , "lock" | "unlock",
	[except_login_name | except_role_name]
	[, number_of_inactive_days]

• Or: 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"]

Sets and displays the global login trigger.

sp_logintrigger <global login trigger name>

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 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, or any user with permission to execute the procedure and update statistics on the target table, to modify the density values of columns in sysstatistics.

sp_modifystats [database].[owner].table_name,
	{"column_group" | "all"}, MODIFY_DENSITY,
	{range | total}, {absolute | factor}, "value"

• Or: 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 | statement], [cpu | diskio | elapsed time]]  
	[event, [spid ]]
	[procedure, [dbname, [procname[, summary | detail]]]]
	[enable] [disable] 
	[help], 
	[deadlock][procstack]

Displays cache usage statistics regarding metadata descriptors for indexes, objects, and databases.

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]]]

Shows option values.

sp_options [ [show | help
	[, option_name | category_name | null
	[, dflt | non_dflt | null [, spid] ] ] ] ]

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]

An interface that a user with sso_role can use to configure login and password policy options.

To specify, remove, and list new password complexity options:

sp_passwordpolicy {“set” | “clear” | “list”}, policy_option, option_value

To verify the password complexity options:

sp_passwordpolicy 'validate password options'

To generate asymmetric key pairs for network login password encryption:

sp_passwordpolicy "regenerate keypair"

To expire passwords:

sp_passwordpolicy "expire role passwords", "[rolename | wildcard]"

sp_passwordpolicy "expire login passwords", "[login_name | wildcard]"

sp_passwordpolicy "expire stale role passwords", "datetime"

sp_passwordpolicy "expire stale login passwords", "datetime"

Manages the Java PCI Bridge. Enables or disables arguments and directives, changes configuration values, and reports configuration values.

sp_pciconfig {	disable { directive | argument } |
	enable { directive | argument } |
	list { list_type [, formatted ] | units | units, units_type[, formatted ] } |
	report { directive[, formatted ] |
		directive, args[, formatted ] |
		argument[, formatted ] } |
	update { number_arg, old_value new_value } }

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. To create a memory pool in an existing cache, or to change pool size:

sp_poolconfig cache_name[, "mem_size [P | K | M | G]", "config_poolK" 
	[, "affected_pool K"], instance instance_name]

To change a pool’s wash size:

sp_poolconfig cache_name, "affected_poolK", "wash=size[P|K|M|G]"

To change a pool’s asynchronous prefetch percentage:

sp_poolconfig cache_name, "affected_poolK", 
	"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.

sp_processmail [subject] [, originator [, dbuser
	[, dbname [, filetype [, separator]]]]]

Displays or changes the execution modes associated with stored procedures.

sp_procxmode [procname [, tranmode]]

Tivoli Storage Manager only – queries the Tivoli Storage Manager (TSM) for a list of the Adaptive Server backup objects.

sp_querysmobj “syb_tsm”, “output_file”, “server_name”
	{, “database_name”, “object_name”, “dump_type”,
	“until_time”, “bs_name”} 

Causes each stored procedure and trigger that uses the named table to be recompiled the next time it runs.

sp_recompile objname

For cluster environments – provides an interface to perform various disk refit-related actions, such as showing the current status of the disk refit process, resetting the state of the disk refit process, skipping the disk refit process for an instance, and so on.

sp_refit_admin [‘help’] | 'status' | ['reset’ | 'skiperfit' [, instance_name]]
	|[ 'removedevice’, device_name]

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.

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]]

Run once in each database containing the tables marked for incremental transfer to create the spt_TableTransfer table in this database.

sp_setup_table_transfer

Prints all the server options that have been set in the current session.

sp_show_options

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 the showplan output for the current SQL statement without specifying the batch_id, context_id, or stmt_num:

  sp_showplan spid, null, null, null
  

Displays execution class, current priority, and affinity for all client sessions running on Adaptive Server.

sp_showpsexe [spid]

Reports the space usage for a table, index, or transaction log and estimates the amount of fragmentation for tables and indexes in a database.

The “help” action syntax:

sp_spaceusage   'help'[, 'all']

sp_spaceusage 'help' [, {'display' |  'display summary'
	| 'report' | 'report summary' | 'archive'} 
	[, {'table' | 'index' | 'tranlog'}]]

The “display” action syntax:

sp_spaceusage 'display summary [using unit= 
	{KB | MB | GB | PAGES} ]', {'table' | 'index'}, name
	[,where_clause [,order_by[,command ] ] ]

sp_spaceusage 'display [using unit= {KB | MB | GB | PAGES} ]',
	{'table' | 'index'}, name 
	[,select_list [,where_clause [,order_by[,command] ] ] ]

sp_spaceusage 'display [using unit={KB | MB | GB | PAGES} ]', 
	'tranlog' [, name[,select_list[,where_clause [,order_by]]]]

The “archive” action syntax:

sp_spaceusage 'archive [ using_clause ]', 
	{'table' | 'index'}, name[,where_clause[,command] ]

sp_spaceusage 'archive [ using_clause ]',
	'tranlog' [,name[,where_clause] ]

The “report” action syntax:

sp_spaceusage 'report summary [ using_clause ]', 
	{'table' | 'index'}, name 
	[,where_clause [,order_by[,from_date [,to_date]]]]

sp_spaceusage 'report [ using_clause ]', 
	{'table' | 'index'}, name 
	[,select_list[,where_clause [,order_by[,from_date [,to_date]]]]]

sp_spaceusage 'report [ using_clause ]', 
	'tranlog' [, name 
	[,select_list[,where_clause [,order_by 
	[,from_date [,to_date]]]]]]
		using_clause = USING using_item [, using_item ...]
		using_item = { unit={ KB | MB | GB | PAGES } 
	| dbname=database_name | prefix=string }

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 { end_sample | interval }[, section[, applmon] ]
	[, 'cache wizard'  [, top_N [, filter] ] ]

Lists tables with suspect partitions. A range-partitioned table on character-based partition keys can become suspect after a sort-order change, and hash-partitioned tables can become suspect after a cross-platform dump load.

sp_tab_suspectptn [table_name]

Provides the binding interface for maintaining bindings in sysattributes that are related to the multiple temporary database.

sp_tempdb [
	[ { “create” | “drop” } , “groupname” ] | 
	[ { “add” | “remove” } , “tempdbname”, “groupname” ] | 
	[ { “bind”, “objtype”, “objname”, “bindtype”, “bindobj” 
		[, “scope”, “hardness” ] } | 
		{ “unbind”, “objtype”, “objname” [, “scope” ] “instance_name”} ] | 
	[ “unbindall_db”, “tempdbname” ] | 
	[ show [, "all" | "gr" | "db" | "login" | "app" [, “name” ] ] | 
	[ who, “dbname”] 
	[ help ] ]

In cluster environments – places a local system temporary database in the drop state.

sp_tempdb_markdrop database_name [, {'mark' | 'unmark'}]

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

If you cannot make a transaction dump on a database, sp_tran_dumable_status displays the reasons the dump is not possible.

sp_tran_dumpable_status [database_name]

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]]

Creates and manages the proxy tables used in the Adaptive Server Web Services Engine.

Creates a proxy table:

sp_webservices 'add', 'wsdl_uri' [, sds_name] 
		[, 'method_name=proxy_table
		 [,method_name=proxy_table ]* ' ]

Displays usage information for sp_webservices:

sp_webservices help [, ’option’]

Lists the proxy tables mapped to a WSDL file:

sp_webservices 'list' [, 'wsdl_uri'] [, sds_name]

Modifies timeout setting:

sp_webservices 'modify', 'wsdl_uri', 'timeout=time'

Removes proxy tables mapped to a WSDL file:

sp_webservices 'remove', 'wsdl_uri' [, sds_name]

Options for user-defined Web services:

Creates a database alias for user-defined Web services:

sp_webservices 'addalias' alias_name , database_name

Deploys a user-defined Web service:

sp_webservices 'deploy', ['all' | 'service_name']

Drops a database alias in user-defined Web services:

sp_webservices 'dropalias' alias_name

Lists the proxy tables mapped to a WSDL file in user-defined Web services:

sp_webservices 'listudws' [, 'service_name']

Lists a database alias or aliases for a user-defined Web service.

sp_webservices 'listalias'

Undeploys a user-defined Web service:

sp_webservices 'undeploy', ['all' | 'service_name']

Reports information about all current Adaptive Server users and processes or about a particular user or process.

sp_who [loginame | "spid"]