Commands

These are very brief descriptions and syntax for Adaptive Server commands. See Reference Manual: Commands for complete information.

Increases the amount of space allocated to a database, as well as to the modified pages section of an archive database.

alter database database_name 
	[on {default | database_device} [= size]
		[, database_device [= size]]...] 
	[log on {default | database_device} [= size]
		[, database_device [= size]]...] 
	set { [durability = { no_recovery | at_shutdown | full}]
      		 [[,] dml_logging = {full | minimal} ]
    		 [[,] template = { database_name | NULL}]}
	[with override]
	[for load]
	[for proxy_update]

Changes the current password for an encryption key.

alter encryption key [[database.][owner].] keyname
	{ [ as | not default ]
		| [ with passwd 
			'password' | system_encr_passwd | login_passwd ]
			modify encryption 
			 [ with passwd 
			'passwd' | system_encr_passwd | login_passwd ]
		| with passwd 'password'
			add encryption [ with passwd 'password' ] 
			for user user_name
			[ for login_association | for recovery ]
		| drop encryption for
			{ user user_name | recovery } 
		| [ with passwd 'password ']
			recover encryption with passwd 'password'
		| modify owner user_name
}

Defines mutually exclusive relationships between roles; adds, drops, and changes passwords for roles; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified role. Also used to lock and unlock roles.

alter role role1 {add | drop} exclusive 
	{membership | activation} role2 

alter role role_name [add passwd "password" | 
	drop passwd] [lock | unlock]

alter role {role_name | "all overrides"} 
	set {passwd expiration | min passwd length | 
	max failed_logins} option_value 

• Adds new columns to a table; drops or modifies existing columns; adds, changes, or drops constraints; changes properties of an existing table; enables or disables triggers on a table.

• Supports adding, dropping, and modifying computed columns and to enable the materialized property, nullability, or definition of an existing computed column to be changed.

• Partitions and repartitions a table with specified partition strategy, or add partitions to a table with existing partitions. Syntax for altering table partitions is listed separately. See the alter table syntax for partitions.

alter table [[database.][owner].table_name
	{add column_name datatype}
		[default {constant_expression | user | null}]
		{identity | null | not null} 
		[off row | in row]
		[[constraint constraint_name]
		{{unique | primary key}
			[clustered | nonclustered]
			[asc | desc]
			[with {fillfactor = pct,
				max_rows_per_page = num_rows,
				reservepagegap = num_pages]
				transfer table [on | off]}
			[on segment_name]
		| references [[database.]owner.]ref_table 
			[(ref_column)]
			[match full]
		| check (search_condition)]
		[encrypt [with [database.[owner].] keyname]
			[decrypt_default {constant_expression | null}]]
		[, next_column]...
	| add [constraint constraint_name]
		{unique | primary key}
			[clustered | nonclustered]
			 (column_name [asc | desc]	[, column_name [asc | desc]...])
			[with {fillfactor = pct,
				max_rows_per_page = num_rows, 
				reservepagegap = num_pages}]
			[on segment_name]
		| foreign key (column_name [{, column_name}...])
			references [[database.]owner.]ref_table
			[(ref_column [{, ref_column}...])]
		[match full]
	| check (search_condition)}
	| set dml_logging = {full | minimal | default}
	| drop {column_name [, column_name]... 
		| constraint constraint_name}
	| modify column_name 	
		[datatype [null | not null]]
		[[[encrypt [with keyname] [decrypt_default [value]]
			| decrypt
		]
				[, next_column]...
	| replace column_name
		default {constant_expression | user | null}
		| decrypt_default {constant_expression | null}
		| drop decrypt_default	|
	lock {allpages | datarows | datapages} }
	| with exp_row_size=num_bytes
	| partition number_of_partitions
	| unpartition
	| partition_clause
	| add_partition_clause

alter table syntax for partitions:

partition_clause::=
	partition by range (column_name[, column_name]...) 
		 ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}] ...) [on segment_name] 
			[, [partition_name] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) [on segment_name]]...)

	| partition by hash (column_name[, column_name]...)
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name[, segment_name] ...)]} 

	| partition by list (column_name) 
		 ([partition_name] values (constant[, constant] ...) 
			[on segment_name] 
			[, [partition_name] values (constant[, constant] ...) 
				[on segment_name]] ...)

	| partition by roundrobin 
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name [, segment_name]...)]}

add_partition_clause::=
	add partition 
		{ ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}]...) 
			[on segment_name] 
			[, [partition_name ] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) 
				[on segment_name]]...) 

		| ([partition_name] values (constant[, constant] ...) 
			[on segment_name] 
			[, [partition_name] values (constant[, constant] ...) 
				[on segment_name]] ...)} 

alter table syntax for computed columns

alter table 
	add column_name {compute | as}
		computed_column_expression...
			[materialized | not materialized]
	drop column_name 
	modify column_name {null | not null | 
		{materialized | not materialized} [null | not null] | 
		{compute | as} computed_column_expression 
			[materialized | not materialized]
			[null | not null]}

alter table syntax for dropping partitions

alter table table_name drop partition
	partition_name [, partition_name]...

alter table syntax for partitions:

partition_clause::=
	partition by range (column_name[, column_name]...) 
		 ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}] ...) [on segment_name] 
			[, [partition_name] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) [on segment_name]]...)

	| partition by hash (column_name[, column_name]...)
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name[, segment_name] ...)]} 

	| partition by list (column_name) 
		 ([partition_name] values (constant[, constant] ...) 
			[on segment_name] 
			[, [partition_name] values (constant[, constant] ...) 
				[on segment_name]] ...)

	| partition by roundrobin 
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name [, segment_name]...)]}

add_partition_clause::=
	add partition 
		{ ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}]...) 
			[on segment_name] 
			[, [partition_name ] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) 
				[on segment_name]]...) 

		| ([partition_name] values (constant[, constant] ...) 
			[on segment_name] 
			[, [partition_name] values (constant[, constant] ...) 
				[on segment_name]] ...)} 

Encloses a series of SQL statements so that control-of-flow language can affect the performance of the whole group.

begin 
	statement block
end

Marks the starting point of a user-defined transaction.

begin tran[saction] [transaction_name]

Causes an exit from a while loop. break is often activated by an if test.

while logical_expression 
	statement 
break 
	statement 
continue

Writes all dirty pages to the database device.

checkpoint [all | [dbname[, dbname, dbname, ........]]

Deactivates a cursor.

close cursor_name

Marks the ending point of a user-defined transaction.

commit [tran | transaction | work] [transaction_name]

Generates summary values that appear as additional rows in the query results.

start_of_select_statement
	compute row_aggregate (column_name) 
		[, row_aggregate (column_name)]... 
	[by column_name [, column_name]...]

Component Integration Services only – connects to the specified server and disconnects the connected server. Creates a passthru to a different server:

connect to server_name
disconnect
	[from ASE]
	[all]
	[connection_name]

• Opens a new JDBC-level connection to Adaptive Server, and does not use CIS. You can specify the arguments in any order. If you do not include an arguments, Adaptive Server prompts you for connection parameters:

  connect
  		[to ASE engine_name]
  		[database database_name]
  		[as connection_name]
  		[user user_id]
  		[identified by password]]]
  

• Opens a new JDBC-level connection to Adaptive Server. This syntax does not use CIS:

  connect using connect_string
  

Restarts the while loop. continue is often activated by an if test.

while boolean_expression 
		statement 
	break 
		statement 
continue

Creates an archive database.

create archive database db_name
	[on db_device [= size]
		[, db_device [= size] ] ... ] 
	with scratch_database = db_name

Creates a new database. Syntax for non-clustered environments:

create [inmemory] [temporary] database database_name
	[use database_name as template]
	[on {default | database_device} [= size] 
		[, database_device [= size]]...] 
	[log on database_device [= size] 
		[, database_device [= size]]...]
	[with {dbid = number, default_location = "pathname", override}]
		| [[,]durability = { no_recovery
			| at_shutdown
			| full} ]
		}...
	[for {load | proxy_update}]

Syntax for cluster environments:

create [ [ global | system ] temporary ] database database_name 
	[ for instance instance_name ]
	[on {default | database_device} [= size] 
		[, database_device [= size]]...] 
	[log on database_device [= size] 
		[, database_device [= size]]...]
	[with {override | default_location = "pathname"}]
	[for {load | proxy_update}]

Specifies a value to insert in a column if no value is explicitly supplied at insert time.

create default [owner.]default_name 
	as constant_expression

Creates user-specified passwords on keys.

create encryption key [ db.[owner ]. ] keyname  [as default] 
	[ for algorithm_name ]
	[ with { [ keylength num_bits ]
	[ passwd 'password_phrase' ]
	[ init_vector {NULL | random } ] 
	[ pad {NULL | random } ] } ]

Component Integration Services only – creates a proxy table, then retrieves and stores metadata from a remote table and places the data into the proxy table. Allows you to map the proxy table to a table, view, or procedure at a remote location.

create existing table table_name (column_list) 
	[on segment_name]
	[[external {table | procedure | file | connection_type}] at pathname 
	[column delimiter "string"]]

Creates a user-defined function, which is a saved Transact-SQL routine that returns a specified value.

create function [ owner_name. ] function_name 
	( [ { @parameter_name [as] parameter_datatype [ = default ] } 
		[ ,...n ] ] ) 
	returns return_datatype
	[ with recompile] ] 
	as 
	[begin]
	function_body 
	return scalar_expression
	[end]

Creates a user-defined function by adding a SQL wrapper to a Java static method. Can return a value defined by the method.

create function [owner.]sql_function_name
		 ([ sql_parameter_name sql_datatype 
			[(length)| (precision[, scale ])]
		[[, sql_parameter_name sql_datatype 
			[(length)| (precision[, scale])]]
		...]])
	returns sql_datatype 
		[(length)| (precision[, scale])]
	[modifies sql data] 
	[returns null on null input | 
		called on null input]
	[deterministic | not deterministic]
	[exportable]
	language java 
	parameter style java
	external name 'java_method_name 
		[([java_datatype[, java_datatype 
		...]])] '

Creates an index on one or more columns in a table, computed or non-computed.

create [unique] [clustered | nonclustered] index index_name 
	on [[database.]owner.]table_name 
		 (column_expression [asc | desc]
			[, column_expression [asc | desc]]...) 
	[with {fillfactor = pct, 
			max_rows_per_page = num_rows,
			reservepagegap = num_pages, 
			consumers = x, ignore_dup_key, sorted_data,
			[ignore_dup_row | allow_dup_row], 
 			 statistics using num_steps values}]
	[on segment_name]
	[index_partition_clause]

• Creates index partitions:

  index_partition_clause::=
  		[local index [partition_name [on segment_name]
  		[, partition_name [on segment_name]...]]]
  

• Creates function-based indexes:

  create [unique | nonclustered] index index_name
  		on [[database.] owner.] table_name
  		 (column_expression [asc | desc] 
  		[, column_expression [asc | desc]]...
  

Creates an abstract plan.

create plan query plan 
	[into group_name]
	[and set @new_id]

Creates a stored procedure or an extended stored procedure that can take one or more user-supplied parameters.

create procedure [owner.]procedure_name[;number]
	[[(@parameter_name datatype [(length) | (precision  [, scale])]
		[= default][output]
	[, @parameter_name datatype [(length) | (precision  [, scale])]
		[= default][output]]...)]]
	[with recompile] 
	as {SQL_statements | external name dll_name}

Creates a SQLJ stored procedure by adding a SQL wrapper to a Java static method.

create procedure [owner.]sql_procedure_name
	 ([[in | out | inout] sql_parameter_name 
		sql_datatype [(length) | 
		 (precision[, scale])]
		[=default]
	...])
	[, [in | out | inout] sql_parameter_name
		sql_datatype [(length) | 
		 (precision[, scale])]]
		[=default]
	...])
	[modifies sql data] 
	[dynamic result sets integer] 
	[deterministic | not deterministic] 
	language java 
	parameter style java
	external name 'java_method_name 
		[([java_datatype[, java_datatype
		...]])]'

Component Integration Services only – creates a proxy table without specifying a column list.

create proxy_table table_name
	[external [table | directory | file]]
	at pathname
	[column delimiter “<string>”]

Creates a user-defined role; specifies the password expiration interval, the minimum password length, and the maximum number of failed logins allowed for a specified role at creation.

create role role_name [with passwd "password" 
	[, {passwd expiration | min passwd length | 
	max failed_logins} option_value]]

Specifies the domain of acceptable values for a particular column or for any column of a user-defined datatype, and creates access rules.

create [[and | or] access]] rule
	[owner.]rule_name 
	as condition_expression

Creates a new collection of tables, views, and permissions for a database user.

create schema authorization authorization_name
	create_object_statement 
		[create_object_statement ...]
	[permission_statement ...]

Wraps the supplied SQL statement in a stored procedure with the specified name and parameters.

create service service-name [secure security_options ] [, userpath path] 
			[, alias alias-name]
	type { xml | raw | soap } 
		[[(@parameter_name datatype [(length ) | (precision  [, scale ])]
			[= default][output]
	[, @parameter_name datatype [(length ) | (precision  [, scale ])]
			[= default][output]]...[)]]
as SQL_statements 

security_options ::= (security_option_item [security_option_item])

Creates new tables and optional integrity constraints, defines computed columns when a table is created, defines encrypted columns and decrypt defaults on encrypted columns when you create a table, and defines the table’s partition property when the table is created.

create table [[database.[owner].]table_name (column_name datatype
	[default {constant_expression  | user | null}]
	[{identity | null | not null}]
		[off row | [in row [(size_in_bytes)]]
	[[constraint constraint_name]
		{{unique | primary key}
		[clustered | nonclustered] [asc | desc]
		[with {fillfactor = pct,
				max_rows_per_page = num_rows,}
				reservepagegap = num_pages]
				dml_logging = {full | minimal}
				transfer table [on | off]}
		[on segment_name]
		| references [[database.]owner.]ref_table
			[(ref_column)]
			[match full]
			| check (search_condition)}]}
	[[encrypt [with key_name] 
		[decrypt_default constant_expression | null]]
	[[constraint [[database.[owner].]key_name]
		{unique | primary key} 
			[clustered | nonclustered]
			 (column_name [asc | desc]
				[{, column_name [asc | desc]}...])
			[with {fillfactor = pct 
				max_rows_per_page = num_rows,
				reservepagegap = num_pages}]
			[on segment_name]
		| foreign key (column_name [{,column_name}...])
			references [[database.]owner.]ref_table
				[(ref_column [{, ref_column}...])]
				[match full]
		| check (search_condition) ...}
	[{, {next_column | next_constraint}}...])
	[lock {datarows | datapages | allpages}]
	[with {max_rows_per_page = num_rows, 
			exp_row_size = num_bytes, 
			reservepagegap = num_pages,
			identity_gap = value}] 
	[on segment_name]
	[partition_clause]
	[[external table] at pathname]
	[for load]

Syntax for partitions

partition_clause::=	partition by range (column_name[, column_name]...) 
		 ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}] ...) [on segment_name] 
			[, [partition_name] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) [on segment_name]]...)

	| partition by hash (column_name[, column_name]...)
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name[, segment_name] ...)]} 

	| partition by list (column_name) 
		 ([partition_name] values (constant[, constant] ...) 
			[on segment_name] 
			[, [partition_name] values (constant[, constant] ...) 
				[on segment_name]] ...)

	| partition by roundrobin 
		{ (partition_name [on segment_name] 
			[, partition_name [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name[, segment_name]...)]}

Syntax for computed columns

create table [database.[owner].] table_name
		 (column_name {compute | as} 
			computed_column_expression [materialized | not materialized]}

Syntax for creating a virtually hashed table

create table [database.[owner].]table_name 
. . .
		| {unique | primary key}
		using clustered
		(column_name [asc | desc] [{, column_name [asc | desc]}...])=
		(hash_factor [{, hash_factor}...])
			with max num_hash_values key

Creates a trigger, a type of stored procedure that is often used for enforcing integrity constraints.

create trigger [owner.]trigger_name
	on [owner.]table_name
	{for | instead of} {insert , update , delete}
	as SQL_statements

Or, using the if update clause:

create trigger [owner.]trigger_name
		on [owner.]table_name
		{for {insert , update} | instead of {insert, update, delete}}
		as 
			[if update (column_name) 
				[{and | or} update (column_name)]...] 
				SQL_statements
			[if update (column_name) 
				[{and | or} update (column_name)]... 
				SQL_statements]...

Creates a view.

create view [owner.]view_name
	[(column_name[, column_name]...)] 
	as 
	select [distinct] select_statement
	[with check option]

Database consistency checker checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality.

dbcc addtempdb (dbid |database_name)

dbcc checkalloc [(database_name[, fix | nofix])]

dbcc checkcatalog [(database_name[, fix])

dbcc checkdb [(database_name[, skip_ncindex])]

dbcc checkindex ({table_name | table_id}, index_id
	[, bottom_up[, partition_name | partition_id]])

dbcc checkstorage [(database_name)]

dbcc checktable (table_name | table_id 
	[, skip_ncindex | fix_spacebits | "check spacebits" | 
	bottom_up | NULL[, partition_name | partition_id)

dbcc checkverify (dbname[, tblname[, ignore_exclusions]])

dbcc complete_xact (xid, {["commit", “1pc”] | "rollback"})

dbcc forget_xact (xid)

dbcc dbrepair (database_name, dropdb)

dbcc engine ({offline, [enginenum] | "online"})

dbcc fix_text ({table_name | table_id})

dbcc indexalloc (table_name | table_id, index_id 
	[, optimized | fast | NULL [, fix | nofix | NULL 
	[, partition_name | partition_id]]])

dbcc monitor (increment, <group name>)

dbcc monitor (decrement, <group name>)

dbcc monitor (reset, <group name>)

dbcc pravailabletempdbs

dbcc rebuild_text (table_name | table_id | "all"[, column[, text_page 
	[, data_partition_name | data_partition_id]]])

dbcc reindex ({table_name | table_id})

dbcc serverlimits

dbcc stackused

dbcc tablealloc (table_name | table_id [, full | optimized | fast | NULL 
	[, fix | nofix | NULL [, data_partition_name | data_partition_id]]])

dbcc textalloc (table_name | table_id [, full | optimized | fast | NULL 
	[, fix | nofix | NULL [, data_partition_name | data_partition_id]]])

dbcc {traceon | traceoff} (flag [, flag ...])

dbcc tune ({ascinserts, {0 | 1} , table_name |
		cleanup, {0 | 1} | 
		cpuaffinity, start_cpu {, on| off} | 
		des_greedyalloc, dbid, object_name,
			" {on | off}" | deviochar vdevno, "batch_size" | 
		doneinproc {0 | 1}})

dbcc upgrade_object [ ( dbid | dbname 
	[,[database.[owner].]compiled_object_name' | 
		 'check' | 'default' | 'procedure' | 'rule' | 
		 'trigger' | 'view' 
		 [, 'force' ] ] )

dbcc syntax for clusters only:

dbcc nodetraceon(trace_flag_number)
dbcc nodetraceoff(trace_flag_number)

dbcc set_scope_in_cluster("cluster"|"instance"|"scope")

dbcc quorum

Makes a cursor inaccessible and releases all memory resources committed to that cursor.

deallocate [cursor] cursor_name

Declares the name and type of local variables for a batch or procedure.

Variable declaration:

declare @variable_name datatype 
		[, @variable_name datatype]...

Variable assignment:

select @variable = {expression | select_statement} 
		[, @variable = {expression | select_statement} ...]
		[from table_list] 
		[where search_conditions] 
		[group by group_by_list] 
		[having search_conditions] 
		[order by order_by_list] 
		[compute function_list [by by_list]]

Defines a cursor, by associating a select statement with a cursor name.

declare cursor_name 
	[semi_sensitive | insensitive] [scroll | no scroll] 
		cursor for select_statement 
	[for {read only | update [of column_name_list]}]

Removes rows from a table.

delete
	[top unsigned_integer]
	[from] [[database.]owner.]{view_name|table_name}
	[where search_conditions]
	[plan "abstract plan"]

delete [[database.]owner.]{table_name | view_name} 
	[from [[database.]owner.]{view_name [readpast]|
		table_name 
			[(index {index_name | table_name}
				[prefetch size][lru|mru])]}
			[readpast] 
	[, [[database.]owner.]{view_name [readpast]|
		table_name 
			[(index {index_name | table_name}
				[prefetch size][lru|mru])]
			[readpast]} ...] 
	[where search_conditions]] 
	[plan "abstract plan"]

delete [from] [[database.]owner.]{table_name|view_name}
	where current of cursor_name

Removes statistics from the sysstatistics system table.

delete [shared] statistics table_name
	[partition data_partition_name] 
	[(column_name[, column_name] ...)]

Makes a physical device or file usable by Adaptive Server.

disk init 
	name = "device_name", 
	physname = { 'physical_name' | 'cache_name'} 
	skip_alloc={true | false}, 
	[vdevno = virtual_device_number,]
	size = number_of_blocks 
	[, type = 'inmemory' ]
	[, vstart = virtual_address 
		, cntrltype = controller_number] 
		[, dsync = {true | false}]
	[, directio = {true | false}]
	[, instance = "instance_name"]

Creates a software mirror that immediately takes over when the primary device fails.

disk mirror 
	name = "device_name", 
	mirror = "physicalname" 
	[, writes = {serial | noserial}]
	[clear = {TRUE | FALSE}]

Rebuilds the master database’s sysusages and sysdatabases system tables from information contained in sysdevices.

disk refit

Rebuilds the master database’s sysdevices system table.

disk reinit 
	name = "device_name", 
	physname = "physicalname" , 
	[vdevno = virtual_device_number ,]
	size = number_of_blocks 
	[, vstart = virtual_address 
		, cntrltype = controller_number]
		[, dsync = {true | false}]
	[, directio = {true | false}]
	[, instance = "instance_name"]

Restarts disk mirroring after it is stopped by failure of a mirrored device or temporarily disabled by the disk unmirror command.

disk remirror 
	name = "device_name"

Dynamically increases the size of the device used by Adaptive Server.

disk resize
	name = “device_name”,
	size = additional_space

Suspends disk mirroring initiated with the disk mirror command to allow hardware maintenance or the changing of a hardware device.

disk unmirror 
	name = "device_name" 
	[, side = {"primary" | secondary}] 
	[, mode = {retain | remove}] 

Removes one or more databases from Adaptive Server.

drop database database_name [, database_name] ...

Removes a user-defined default.

drop default [owner.]default_name 
	[, [owner.]default_name] ...

Allows table owners to drop the encryption or encryption key on a column by using alter table with the decrypt option.

drop encryption key [database.[owner].]keyname

Removes one or more user-defined functions from the current database.

drop function{ [ owner_name . ] function_name } [ ,...n ] 

Removes a SQLJ function.

drop func[tion] [owner.]function_name
	[, [owner.]function_name ] ...

Removes an index from a table in the current database.

drop index table_name.index_name 
	[, table_name.index_name] ...

Removes a procedure.

drop proc[edure] [owner.]procedure_name 
	[, [owner.]procedure_name] ... 

Drops a user-defined role.

drop role role_name [with override]

Removes a user-defined rule.

drop rule [owner.]rule_name[, [owner.]rule_name] ...

Removes a user-defined Web service from the current database.

drop service service-name

Removes a table definition and all of its data, indexes, partition properties, triggers, encryption properties, and permissions from the database.

drop table [[database.]owner.]table_name 
	[, [[database.]owner.]table_name] ...

Removes a trigger.

drop trigger [owner.]trigger_name 
	[, [owner.]trigger_name] ...

Removes one or more views from the current database.

drop view [owner.]view_name [, [owner.]view_name] ...

Makes a backup copy of the entire database, including the transaction log, in a form that can be read in with load database.

dump database database_name
	to [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		capacity = number_kilobytes, 
		dumpvolume = volume_name, 
		file = file_name] 
		with verify[= header | full]
	[stripe on [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]] 
	[[stripe on [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		compression = compress_level
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		passwd = password,
		retaindays = number_days,
		[noinit | init],
		notify = {client | operator_console}
		}] 

Copies the database when the Tivoli Storage Manager provides backup services:

dump database database_name 
	to "syb_tsm::object_name"
		[blocksize = number_bytes]
	[stripe on "[syb_tsm::]object_name"
		[blocksize = number_bytes]]...]
	[with {
		blocksize = number_bytes,
		compression = compress_level,
		passwd = password,
		[noinit | init],
		notify = {client | operator_console},
		verify[ = header | full]
		} ]

Makes a copy of a transaction log and removes the inactive portion. To make a routine log dump:

dump tran[saction] database_name  
	to [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::[compression_level::]]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]
	[[stripe on [compress::[compression_level::]]stripe_device 
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes,
		capacity = number_kilobytes, 
		compression = compress_level,
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		retaindays = number_days,
		[noinit | init],
		notify = {client | operator_console}, 
		standby_access}]

Truncates the log without making a backup copy:

dump tran[saction] database_name  
		with truncate_only

Truncates a log that is filled to capacity. Use only as a last resort:

dump tran[saction] database_name  
		with no_log 

Backs up the log after a database device fails:

dump tran[saction] database_name  
		to [compress::[compression_level::]]stripe_device
			[at backup_server_name]
			[density = density_value, 
			blocksize = number_bytes,
			capacity = number_kilobytes, 
			dumpvolume = volume_name,
			file = file_name]
		[stripe on [compress::[compression_level::]]stripe_device 
			[at backup_server_name]
			[density = density_value, 
			blocksize = number_bytes,
			capacity = number_kilobytes, 
			dumpvolume = volume_name,
			file = file_name]]
		[[stripe on [compress::[compression_level::]]stripe_device
			[at backup_server_name]
			[density = density_value, 
			blocksize = number_bytes,
			capacity = number_kilobytes, 
			dumpvolume = volume_name,
			file = file_name]]...]
		[with {
			density = density_value, 
			blocksize = number_bytes,
			capacity = number_kilobytes, 
			compression = compress_level
			dumpvolume = volume_name,
			file = file_name,
			[dismount | nodismount],
			[nounload | unload],
			retaindays = number_days,
			[noinit | init],
			no_truncate, 
			notify = {client | operator_console}}]

Copies the transaction log when the Tivoli Storage Manager provides backup services.

dump transaction database_name 
	to "syb_tsm::object_name"
		[blocksize = number_bytes]
	[stripe on "[syb_tsm::]object_name"
		[blocksize = number_bytes]]...]
	[with {
		blocksize = number_bytes,
		compression = compress_level,
		passwd = password,
		[noinit | init],
		notify = {client | operator_console},
		verify[ = header | full]
		} ]

Runs a procedure or dynamically executes Transact-SQL commands.

[exec[ute]] [@return_status  =]
	[[[server .]database.]owner.]procedure_name[;number] 
		[[@parameter_name =] value  | 
			[@parameter_name =] @variable [output]
		[, [@parameter_name =] value  | 
			[@parameter_name =] @variable [output]...]] 
	[with recompile]

• Or: exec[ute] ("string" | char_variable [+ "string" | char_variable]...)

Returns a row or a set of rows from a cursor result set.

fetch [next |prior | first | last | absolute
	fetch_offset | relative fetch_offset] 
	[from] cursor_name
	[into fetch_target_list]

Branches to a user-defined label.

label: 
	goto label

Assigns permissions to individual users, groups of users, and roles. Assigns roles to users or system or user-defined roles. Grants permission to access database objects:

grant {all [privileges]| permission_list} 
	on {table_name [(column_list)]
		| view_name[(column_list)] 
		| stored_procedure_name}
	to {public | name_list | role_list}
	[with grant option]

Grants permission to use built-in functions:

grant select 
		on [builtin] builtin 
		to {name_list | role_list}

Grants permission to execute certain commands:

grant {all [privileges] | command_list} 
		to {public | name_list | role_list}

Grants access on certain dbcc commands:

grant dbcc {dbcc_command [on {all | database}]
			[, dbcc_command [on {all | database}], ...]} 
		to {user_list | role_list }

Grants permission to create encryption keys:

grant create encryption key to {user_list | role_list  | group_list}

Grants decrypt permission on a table or a list of columns in a table.

grant decrypt on [ owner. ]tablename[(columnname [{,columname}])] 
		to {user | group | role}

Grants the default permissions for specific system tables:

grant default permissions on system tables

Grants a role to a user or a role:

grant {role role_granted [, role_granted ...]}
		to grantee [, grantee...]

Switches your server user identity to any other server login and limit its use based on the target login roles:

grant set proxy to role_list
		[restrict role role_list | all | system]

Used in select statements to divide a table into groups and to return only groups that match conditions in the having clause.

Start of select statement

[group by [all] aggregate_free_expression
	[, aggregate_free_expression]...]

[having search_conditions]

End of select statement

Imposes conditions on the execution of a SQL statement.

if logical_expression [plan "abstract plan"]
	statements

[else 
	[if logical_expression] [plan "abstract plan"]
		statement]

Adds new rows to a table or view.

insert [into] [database.[owner.]]{table_name|view_name}
	[(column_list)] 
	{values (expression [, expression]...)
		|select_statement [plan "abstract plan"]}

Kills a process.

kill spid with statusonly

Loads a backup copy of a user database, including its transaction log, that was created with dump database, as well as materialize archive databases that have been loaded with a database dump. To make a routine database load:

• Makes a routine database load:

  load database database_name 
  	from [compression=]stripe_device 
  		[at backup_server_name]
  		[density = density_value, 
  		blocksize = number_bytes, 
  		dumpvolume = volume_name, 
  		file = file_name] 
  		with verify only [= header | full]
  	[stripe on [compression=]stripe_device 
  		[at backup_server_name]
  		[density = density_value, 
  		blocksize = number_bytes,
  		dumpvolume = volume_name, 
  		file = file_name]
  	[[stripe on [compression=]stripe_device 
  		[at backup_server_name]
  		[density = density_value, 
  		blocksize = number_bytes, 
  		dumpvolume = volume_name, 
  		file = file_name]]...]
  	[with {
  		density = density_value, 
  		blocksize = number_bytes, 
  		compression, 
  		dumpvolume = volume_name, 
  		file = file_name, 
  		[dismount | nodismount],
  		[nounload | unload],
  		passwd = password, 
  		notify = {client | operator_console},
  		[override]}]]
  

• Returns header or file information without loading the backup:

  load database database_name 
  	from [compress::]stripe_device 
  		[at backup_server_name] 
  		[density = density_value, 
  		blocksize = number_bytes, 
  		dumpvolume = volume_name, 
  		file = file_name]
  	[stripe on [compress::]stripe_device
  		[at backup_server_name] 
  		[density = density_value, 
  		blocksize = number_bytes, 
  		dumpvolume = volume_name, 
  		file = file_name] 
  	[[stripe on [compress::]stripe_device 
  		[at backup_server_name] 
  		[density = density_value, 
  		blocksize = number_bytes, 
  		dumpvolume = volume_name, 
  		file = file_name]]...] 
  	[with {
  		density = density_value, 
  		blocksize = number_bytes, 
  		compression, 
  		dumpvolume = volume_name, 
  		file = file_name, 
  		[dismount | nodismount],
  		[nounload | unload],
  		passwd = password, 
  		listonly [= full],
  		headeronly, 
  		notify = {client | operator_console}
  		}]]
  

• Materializes an archive database:

  load database database_name
  	from dump_device
  	[ [stripe on stripe_device] ... ]
  	[with [norecovery,][passwd=password]
  

• Loads a copy of the database when the Tivoli Storage Manager is licensed at your site:

  load database database_name 
  	from syb_tsm::[[-S source_sever_name][-D source_database_name]
  		::]object_name [blocksize = number_bytes]
  	[stripe on syb_tsm::[[-S source_sever_name]
  		[-D source_database_name]::]object_name
  		[blocksize = number_bytes]]
  	[[stripe on syb_tsm::[[-S source_sever_name]
  		[-D source_database_name]::]object_name
  		[blocksize = number_bytes]]...]
  	[with {
  		blocksize = number_bytes,
  		passwd = password,
  		listonly [= full],
  		headeronly,
  		notify = {client | operator_console},
  		[[verifyonly | verify] [= header | full]]
  		} ]
  

Loads a backup copy of the transaction log that was created with dump transaction. To make a routine log load:

Makes a routine log load:

load tran[saction] database_name
	from [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name,
		file = file_name]
	[[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes, 
		compression, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		notify = {client | operator_console}
		}]]

Returns header or file information without loading the backup log:

load tran[saction] database_name
	from [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name,
		file = file_name]
	[[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes, 
		compression, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		listonly [= full],
		headeronly,
		notify = {client | operator_console}
		until_time = datetime}]]

Loads a transaction log into an archive database:

load tran[saction] database_name
	from dump_device
	[[stripe on stripe_device] ... ]

Tivoli Storage Manager only – loads a copy of the transaction log when the Tivoli Storage Manager is licensed at your site:

load transaction database_name 
	from syb_tsm::[[-S source_sever_name][-D source_database_name]
		::]object_name [blocksize = number_bytes]
	[stripe on syb_tsm::[[-S source_sever_name]
		[-D source_database_name]::]object_name
		[blocksize = number_bytes]]
	[[stripe on syb_tsm::[[-S source_sever_name]
		[-D source_database_name]::]object_name
		[blocksize = number_bytes]]...]
	[with {
		blocksize = number_bytes,
		passwd = password,
		listonly [= full],
		headeronly,
		notify = {client | operator_console},
		until_time = datetime
		} ]

Explicitly locks a table within a transaction.

lock table table_name in {share | exclusive} mode
	[wait [numsecs] | nowait]

Attaches a database to a destination or secondary Adaptive Server.

mount database all | database_mapping[, database_mapping, ...]
	from "manifest_file" 
	[using device_mapping [, device_mapping...] 
		[with listonly]

	database_mapping:
			origdbname as newdbname
		|	newdbname = origdbname
		|	origdbname
		|	newdbname

	device_mapping
			logical_device_name as new_physical_name
		|	new_physical_name = logical_device_name
		|	original_physical_name
		|	new_physical_name

Marks a database available for public use after a normal load sequence.

online database database_name [for standby_access]

Opens a cursor for processing.

open cursor_name

Returns query results in the specified columns in sorted order.

[Start of select statement]

[order by
	{[table_name.| view_name.]
		column_name | select_list_number | expression}
		[asc | desc] 
	[,{[table_name.| view_name.]
		column_name | select_list_number | expression}
		[asc | desc]]...]

[End of select statement]

Used by DB-Library in a two-phase commit application to see if a server is prepared to commit a transaction.

prepare tran[saction]

Prints a user-defined message on the user’s screen.

print 
	{format_string | @local_variable | 
	@@global_variable}
		[, arg_list]

Suspends and resumes updates to a specified list of databases.

quiesce database tag_name hold database_list [for external dump] 
	[to manifest_file [with override]]

Or:

quiesce database tag_name release

Prints a user-defined error message on the user’s screen and sets a system flag to record that an error condition has occurred.

raiserror error_number 
	[{format_string | @local_variable}] [, arg_list]
	[with errordata restricted_select_list]

Reads text, unitext, and image values, starting from a specified offset and reading a specified number of bytes or characters.

readtext [[database.]owner.]table_name.column_name
	text_pointer offset size 
	[holdlock | noholdlock] [readpast]
	[using {bytes | chars | characters}]
	[at isolation {
		[read uncommitted | 0] | 
		[read committed | 1] |
		[repeatable read | 2]| 
		[serializable | 3]}]

No effect; it is included to allow existing scripts to run without modification.

reconfigure

Removes one or more Java-SQL classes, packages, or JARs from a database, when Java classes are installed in the database.

remove java 
	class class_name[, class_name]...
		| package package_name[, package_name]...
		| jar jar_name[, jar_name]...[retain classes]

Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.

reorg compact table_name [partition partition_name]
	[with {resume, time = no_of_minutes}]

reorg forwarded_rows table_name [partition partition_name] 
	[with {resume, time = no_of_minutes}]

reorg rebuild table_name [index_name [partition index_partition_name]]

reorg reclaim_space table_name [index_name] [partition partition_name]
	[with {resume, time = no_of_minutes}]

Exits from a batch or procedure unconditionally and provides an optional return status.

return [integer_expression] [plan "abstract_plan"]

Revokes permissions or roles from users, groups, or roles. To revoke permission to access database objects:

revoke [grant option for] 
	{all [privileges] | permission_list} 
	on {table_name [(column_list)] 
		| view_name [(column_list)] 
		| stored_procedure_name}
	from {public | name_list | role_list}
	[cascade]

Revokes permission to select built-in functions:

revoke select 
		on [builtin] builtin 
		to {name_list | role_list}

Revokes permission to create database objects, execute set proxy, or execute set session authorization:

revoke {all [privileges] | command_list} 
		from {public | name_list | role_list}

Revokes a role from a user or another role:

revoke role {role_name [, role_list ...]} from 
		{grantee [, grantee ...]}

Revokes access on some dbcc commands:

revoke dbcc {dbcc_command [on {all | database}]
			[, dbcc_command [on {all | database}], ...]} 
		from {user_list | role_list}

Revokes permission from other users, groups, and roles to create encryption keys.

revoke create encryption key from user | role | group

Revokes decrypt permission on a table or a list of columns in a table:

revoke decrypt on [owner.] tablename[(columnname 	[{,columname}])] 
		from user | group | role

Revokes the default permissions from public:

revoke default permissions on system tables

Rolls back a user-defined transaction to the named savepoint in the transaction or to the beginning of the transaction.

rollback [tran | transaction | work]
	[transaction_name | savepoint_name]

Rolls back the work done in a trigger, including the data modification that caused the trigger to fire, and issues an optional raiserror statement.

rollback trigger
	[with raiserror_statement]

Sets a savepoint within a transaction.

save transaction savepoint_name 

Retrieves rows from database objects.

select ::=
	select [all | distinct]
	[top unsigned_integer]
	select_list
	[into_clause]
	[from_clause]
	[where_clause]
	[group_by_clause]
	[having_clause]
	[order_by_clause]
	[compute_clause]
	[read_only_clause]
	[isolation_clause]
	[browse_clause]
	[plan_clause]

select_list ::=

into_clause ::= 
	into [[database.] owner.] table_name 
		[(colname encrypt [with [database.[owner].]keyname] [, 
			colname encrypt_clause ...])]
		[{[external table at]
			'server_name.[database].[owner].object_name’ 
			| external directory at ‘pathname’ 
			| external file at ‘pathname’ [column delimiter ‘string’]}]
		[on segment_name]
		dml_logging = (full | minimal)
		[partition_clause]
		[lock {datarows | datapages | allpages}]
		[with [, into_option[, into_option] ...]]]

	| into existing table table_name 

	partition_clause ::=
		 partition by range (column_name[, column_name]...) 
			 ([partition_name] values <= ({constant | MAX} 
				[, {constant | MAX}] ...) [on segment_name] 
				[, [partition_name] values <= ({constant | MAX} 
					[, {constant | MAX}] ...) [on segment_name]]...)

		| partition by hash (column_name[, column_name]...)
			{ (partition_name [on segment_name] 
				[, partition_name [on segment_name]]...) 
			| number_of_partitions 
				[on (segment_name[, segment_name] ...)]} 

		| partition by list (column_name) 
			 ([partition_name] values (constant[, constant] ...) 
				[on segment_name] 
				[, [partition_name] values (constant[, constant] ...) 
					[on segment_name]] ...)

		| partition by roundrobin 
			{ (partition_name [on segment_name] 
				[, partition_name [on segment_name]]...) 
			| number_of_partitions 
				[on (segment_name [, segment_name]...)]}

	into_option ::= 
		| max_rows_per_page = num_rows
		| exp_row_size = num_bytes
		| reservepagegap = num_pages
		| identity_gap = gap 

from_clause ::=
	from table_reference [,table_reference]...

	table_reference ::= 
		table_view_name | ANSI_join

		table_view_name ::=
			[[database.]owner.] {{table_name | view_name} 
			[as] [correlation_name]
			[(index {index_name | table_name})] 
			[parallel [degree_of_parallelism]]
			[prefetch size][lru | mru]}
		[holdlock | noholdlock] 
		[readpast]
		[shared]

		ANSI_join ::=
			table_reference join_type join table_reference 
					join_conditions
				join_type ::= inner | left [outer] | right [outer]
				join_conditions ::= on search_conditions

where_clause ::= 
	where search_conditions

group_by_clause ::=
	group by [all] aggregate_free_expression 
		[, aggregate_free_expression]...

having_clause ::=
	having search_conditions

order_by_clause ::=
	order by sort_clause [, sort_clause]...

	sort_clause ::=
		{[[[database.]owner.]{table_name.|view_name.}]column_name 
		| select_list_number 
		| expression }
		[asc | desc]

compute_clause ::=
	compute row_aggregate (column_name)
		[, row_aggregate (column_name)]...
	[by column_name [, column_name]...]

read_only_clause ::=
	for {read only | update [of column_name_list]}

isolation_clause ::=
	at isolation
		{read uncommitted | 0}
		| {read committed | 1}
		| {repeatable read | 2} 
		| {serializable | 3}

browse_clause ::=
	for browse

plan_clause ::=
	plan "abstract plan"

Sets Adaptive Server query-processing options for the duration of the user’s work session; sets some options inside a trigger or stored procedure.

set advanced_aggregation on/off

set @variable = expression [, @variable = expression...]

set ansinull {on | off}

set ansi_permissions {on | off}

set arithabort [arith_overflow | numeric_truncation] {on | off}

set arithignore [arith_overflow] {on | off}

set bulk array size number

set bulk batch size number

set {chained, close on endtran, nocount, noexec, parseonly, 
	self_recursion, showplan, sort_resources} {on | off}

set char_convert {off | on [with {error | no_error}] |
	charset [with {error | no_error}]}

set cis_rpc_handling {on | off}

set [clientname client_name | clienthostname host_name
	| clientapplname application_name]

set cursor rows number for cursor_name

set {datefirst number, dateformat format, language language}

set delayed_commit {on | off | default}

set deferred_name_resolution { on | off }

set dml_logging {minimal | default}

set encryption passwd 'password_phrase'
     for {key | column} {keyname | column_name}

set export_options [on | off]

set fipsflagger {on | off}

set flushmessage {on | off}

set fmtonly {on | off}

set forceplan {on | off}

set identity_insert [database.[owner.]]table_name {on | off}

set identity_update table_name {on | off}

set index_union on | off

set literal_autoparam on | off

set lock {wait [numsecs] | nowait}

set metrics_capture on | off

set offsets {select, from, order, compute, table,
	procedure, statement, param, execute} {on | off}

set option show

set opttimeoutlimit

set parallel_degree number 

set plan {dump | load} [group_name] {on | off}

set plan exists check {on | off}

set plan for show

set plan optgoal {allrows_mix | allrows_dss}

set plan opttimeoutlimit number

set plan replace {on | off}

set prefetch [on|off]

set print_minlogged_mode_override

set proc_output_params {on | off}

set proc_return_status {on | off}

set process_limit_action {abort | quiet | warning}

set proxy login_name

set quoted_identifier {on | off}

set repartition_degree number

set repthreshold number

set resource_granularity number

set role {"sa_role" | "sso_role" | "oper_role" | 
	role_name [with passwd "password"]} {on | off}

set {rowcount number, textsize number}

set scan_parallel_degree number 

set session authorization login_name

set switch [serverwide] {on | off} trace_flag ,[trace_flag,] [with option [, option]

set show_exec_info [“on” | “off”

set show_sqltext {on | off}

set statistics {io, subquerycache, time, plancost} {on | off}

set statistics simulate {on | off}

set strict_dtm_enforcement {on | off}

set string_rtruncation {on | off}

set system_view {instance | cluster | clear}

set textsize {number}

set tracefile [filename] [off] [for spid]

set transaction isolation level {
	[read uncommitted | 0] | 
	[read committed | 1] |
	[repeatable read | 2] | 
	[serializable | 3]} 

set transactional_rpc {on | off}

Allows a Database Owner to impersonate another user.

setuser ["user_name"]

Shuts down the Adaptive Server from which the command is issued, its local Backup Server, or a remote Backup Server.

shutdown [srvname] [with {wait [="hh:mm:ss"] | nowait}]]

Syntax for clusters:

shutdown {cluster | [instance_name]} [with {wait | nowait}]

Initiates an incremental table transfer.

transfer table [[db.]owner.]table [to | from] destination_file
	[ for { ase | bcp | iq | csv } ]
	[ with {column_separator=string}, {column_order=option},
	{encryption=option}, {row_separator=string},
	{resend=id}, {progress=sss}, {tracking_id=nnn} 
	{sync = true | false]}, {fixed_length = true | false}
		, null_byte = true | false}]

Removes all rows from a table or partition.

truncate table [[database.]owner.]table_name 
	[partition partition_name]

Returns a single result set that combines the results of two or more queries. Duplicate rows are eliminated from the result set unless the all keyword is specified.

select [top unsigned_integer] select_list
	[into clause] [from clause] [where clause]
	[group by clause] [having clause]
	[union [all]
	select [top unsigned_integer] select_list
	[from clause] [where clause]
	[group by clause] [having clause]]...
	[order by clause]
	[compute clause]

Shuts down the database and drops it from the Adaptive Server, and deactivates and drops devices.

unmount database dbname_list to manifest_file

Changes data in existing rows by adding data or modifying existing data.

update [top unsigned_integer]
	[[database.]owner.]{table_name | view_name}
	set [[[database.]owner.]{table_name.|view_name.}]
	column_name1 =
	{expression1 | NULL | (select_statement)} |
	variable_name1 =
	{expression1 | NULL | (select_statement)}
	[, column_name2 =
	{expression2 | NULL | (select_statement)}]... |
	[, variable_name2 =
	{expression2 | NULL | (select_statement)}]...

	[from [[database.]owner.]{view_name [readpast]|
		table_name 
			[(index {index_name | table_name}
				[prefetch size][lru|mru])]}
			[readpast] 
		[,[[database.]owner.]{view_name [readpast]|
		table_name 
			[(index {index_name | table_name}
				[prefetch size][lru|mru])]}]
			[readpast] ...]
	[where search_conditions]
	[plan "abstract plan"]

update [[database.]owner.]{table_name | view_name} 
	set [[[database.]owner.]{table_name.|view_name.}]
		column_name1 =
			{expression1 | NULL | (select_statement)} |
		variable_name1 =
			{expression1 | NULL | (select_statement)}
		[, column_name2 = 
			{expression2 | NULL | (select_statement)}]... |
		[, variable_name2 = 
			{expression2 | NULL | (select_statement)}]...
	where current of cursor_name

Updates all statistics information for a given table. You can run update all statistics on a single data partition.

update all statistics table_name [partition data_partition_name]

Updates the statistics for all columns in an index.

update index statistics
	table_name [[partition data_partition_name] |
	[index_name [partition index_partition_name]]]
	[using step values]
	[with consumers = consumers] [, sampling=N percent]

Updates information about the distribution of key values in specified indexes, for all columns in an index, table, or partition, and resets the data change counters for global nonclustered indexes.

update statistics table_name
	[[partition data_partition_name] [(column_list)] |
	index_name [partition index_partition_name]] 
	[using step values]
	[with consumers = consumers][, sampling=N percent]

Updates statistics that are stored in systabstats table.

 update table statistics table_name
	[partition data_partition_name]
	[index_name [partition index_partition_name]]

Specifies the database with which you want to work.

use database_name 

Specifies a specific time, a time interval, or an event for the execution of a statement block, stored procedure, or transaction.

waitfor {delay time | time time | errorexit | processexit | mirrorexit} 

Sets the search conditions in a select, insert, update, or delete statement.

where [not] expression comparison_operator expression

where {[not] expression comparison_operator expression} | {...}

where [not] expression [not] like "match_string"
	[escape "escape_character "]

where [not] expression is [not] null

where [not] expression [not] between expression and expression

where [not] expression [not] in ({value_list | subquery})

where [not] exists (subquery)

where [not] expression comparison_operator {any | all} (subquery)

where [not] column_name join_operator column_name

where [not] logical_expression

where [not] expression {and | or} [not] expression

Sets a condition for the repeated execution of a statement or statement block.

while logical_expression [plan "abstract plan"] statement

Permits minimally logged, interactive updating of an existing text, unitext or image column.

writetext [[database.]owner.]table_name.column_name
	text_pointer [readpast] [with log] data