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

sp_addengine does not run in threaded mode.

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 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 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 the name of the local server; specifies the server for remote procedure calls (RPCs) when using the host and port parameters.

sp_addserver lname [, class [, pname]]

Component Integration Services (CIS) only:

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

Creates a threshold to monitor space on a database segment. When free space on the segment falls below the specified level, Adaptive Server executes the associated stored procedure.

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

A utility stored procedure that produces readable result set data, sp_autoformat reformats the width of variable-length character data to display only non-blank characters. Trailing blanks are truncated in the output.

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, stored procedure, or default execution class.

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. 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. The compiled object may be the user-defined name of a predicate, or if the predicate has no external name, its internal name.

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}

sp_cleanpwdchecks is a custom stored procedure that 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 (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]

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

sp_clusterlockusage

(Cluster environments only) Performs a number of procedures related to clusters.

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—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 is run from the secondary companion.

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.

• Simulate execution of the database expansion machinery, to study the operation before engaging large volume loads.

All arguments are string arguments:

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—the views, triggers, user-defined functions, and procedures—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.

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. By using a wildcard expression (%), you can also obtain information about matching logins. Also displays the encryption versions of the login password stored on disk.

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

Displays all roles granted to another role, login or login profile, the entire hierarchy tree of roles in table format, and other login security-related parameters configured for the specified role, including the date when the role was locked, its reason, and the login server user ID (suid) that locked the role. For password-protected roles, also displays the role password encryption version.

sp_displayroles [grantee_name [, mode]]

(master database only) Validates readiness for downgrade to an earlier 15.0.x release. Also downgrades the system catalog changes that 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

sp_dropengine does not run in threaded mode.

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 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', 
	{'master'|'dual master'} [, 'display_keys' | 'all_dbs'

sp_encryption 'help'[, 'servicekeyname '[, 'display_objs']]

sp_encryption 'helpextpasswd'

sp_encryption helpcol [, table_name | column_name ]

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

sp_encryption 'mkey_startup_file'[, {'new_path' | 'default_location' | 'null'} [, {sync_with_mem | sync_with_qrm}]]

sp_encryption 'downgrade_kek_size' [, 'true'|'false']

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

Enables you to bring an engine online or offline. In threaded mode, use alter thread pool to bring engines online.

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

A custom stored procedure that can contain user-defined logic for password complexity checks. You can configure sp_extrapwdchecks according to your security needs. Install sp_extrapwdchecks in the master database.

sp_extrapwdchecks caller_password, new_password, login_name

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 ]

sp_fixindex repairs a set of indexes (rather than on a single index) on a system table when it has been corrupted. sp_fixindex rebuilds the data layer if the target table has a placement or clustered index (it reclaims the unused space in the data layer while working on the placement or clustered index of a system table).

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 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 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 with Sybase Failover in a high availability system. sp_ha_admin is installed with the installhavss script on UNIX platforms or the insthasv script on Windows NT.

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 user-defined functions, computed columns and function-based indexes. Column displays optimistic_index_lock.

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 ]

Determines which sessions Adaptive Server is tracing. sp_helpapptrace returns the server process IDs (spids) for all the sessions Adaptive Server is tracing, the spids of the sessions tracing them, and the name of the tracefile.

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 (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. Reports information 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]]]

Displays the current thread pool configuration.

sp_helpthread [pool_name]

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. Also identifies objects and user-defined datatypes owned by a users.

sp_helpuser [name_in_db [, display_object]]

Hides the source text for the specified compiled object, as well as the text of computed columns and function-based index keys. sp_hidetext also encrypts the text for user-defined functions.

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 Adaptive Server listeners on any given port on a per-server basis.

For threaded mode, the syntax is:

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

Or:

sp_listener “command”, “[protocol:]machine:port:”CN=common_name”

For process mode, the syntax is:

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

Or:

sp_listener “command”, “[protocol:]machine:port:”CN=common_name”, 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. This global login trigger has the same characteristics as a personal login script. It is executed before any personal login script for every user that tries to log in, including system administrators and security officers.

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)

Removes existing duplicate inline default objects, converting the unique inline defaults to sharable inline default objects.

sp_merge_dup_inline_default [report_only = {yes | no} 
	[, show_progress = {yes | no}]]

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

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, databases, and the kernel resource memory pool. sp_monitorconfig also 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"]

Provides server-wide monitoring information.

sp_monitor_server [server_name]

Shows lock contention, lock wait-time, and deadlock statistics for tables and indexes.

sp_object_stats interval[, top_n[, dbname, objname[, rpt_option]]]

Returns a performance analysis for the selected query.

sp_opt_querystats "query_text" | help [, "diagnostic_options" | null 
[, database_name] [, user_name]]

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

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"

To display a brief description of all commands, options, and their values:

sp_passwordpolicy "help"

Manages the Java PCI Bridge. Enables or disables arguments and directives, changes configuration values, and reports configuration values. Do not use sp_pciconfig to change arguments or directives unless instructed to do so by Sybase Technical Support.

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, using the xp_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]]

(Tivoli Storage Manager only) Queries the Tivoli Storage Manager (TSM) for a list of the Adaptive Server backup objects.sp_querysmobj is supported only when the TSM is licensed at your site.

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

Lists the attributes or bindings associated with a login profile.

sp_securityprofile ‘attributes’,‘login profile’,
	{wildcard | login_profile_name | ‘default’}

sp_securityprofile ‘bindings’, ‘login profile’
	[, {wildcard | login_profile_name | ‘default’} 
	[, ‘login’ ,{wildcard | login_name}]]

sp_securityprofile ‘help’ 

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 thread pool assignments, bound client applications, logins, and stored procedures.

sp_showcontrolinfo [object_type, object_name, spid ]

Displays the execution class attributes and the thread pool name associated with the specified execution class.

sp_showexeclass [execlassname]

Similar in function to the optdiag standalone utility in an XML document but in a system procedure format, sp_showoptstats extracts and displays statistics and histograms for various data objects from system tables such as systabstats and sysstatistics.

sp_showoptstats  [[database_name.[owner].]table_name], [column_name], [h]

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

To display 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 thread pool 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 estimates are computed using an average row-length for data and index rows, and the number of rows in a table. You can archive the space usage and fragmentation data for future reporting and trends analysis. sp_spaceusage supports a number of actions, including help, display, archive and report, to indicate the current Adaptive Server space usage.

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]

sp_tempdb allows users to:

• Create and manage temporary database groups.

• Bind users or applications to the default or other temporary database group or to a specific local temporary database.

• Manage bindings to local temporary databases and temporary database groups.

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.When a threshold is crossed, Adaptive Server passes the following parameters to the threshold procedure by position:

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, stored procedure, or default execution class 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 (installmaster, installdbccdb, and so on) 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.

To create a proxy table:

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

To display usage information for sp_webservices:

sp_webservices help [, ’option’]

To list the proxy tables mapped to a WSDL file:

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

To modify timeout setting:

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

To remove proxy tables mapped to a WSDL file:

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

To create a database alias for user-defined Web services:

sp_webservices 'addalias' alias_name , database_name

To deploy a user-defined Web service:

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

To drop a database alias in user-defined Web services:

sp_webservices 'dropalias' alias_name

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

sp_webservices 'listudws' [, 'service_name']

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

sp_webservices 'listalias'

To undeploy 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. Includes the thread_pool column, which describes the thread pool Adaptive Server uses to execute a task.

sp_who [loginame | "spid"]