Commands

See Reference Manual: Commands for details.

alter database

alter database database_name 
	[on {default | database_device } [= size]
		[, database_device [= size]]...] 
	[log on { default | database_device } [ = size ]
		[ , database_device [= size]]...] 
	[with override]
	[for load]
	[for proxy_update]

alter role

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 

alter table

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 }]
			[on segment_name]
		| references [[database.]owner.]ref_table 
			[(ref_column)]
		| check (search_condition) ] ... }
		[, 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}...])]
		[ 
	| check (search_condition)}

	| drop {column_name [, column_name]... 
		| constraint constraint_name }

	| modify column_name datatype [null | not null] 
		[, next_column]...

	| replace column_name
		default { constant_expression | user | null}

	| partition number_of_partitions

	| unpartition

	| { enable | disable } trigger

	| lock {allpages | datarows | datapages } }

	| with exp_row_size=num_bytes

begin...end

begin 
	statement block
end

begin transaction

begin tran[saction] [transaction_name]

break

while logical_expression 
	statement 
break 
	statement
continue

case

case
	when search_condition then expression
	[when search_condition then expression]...
	[else expression]
end

case and values syntax:

case expression
	when expression then expression
	[when expression then expression]...
	[else expression]
end

checkpoint

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

close

close cursor_name

coalesce

coalesce(expression, expression [, expression]...)

commit

commit [tran | transaction | work] [transaction_name]

compute clause

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

connect to...disconnect

connect to server_name
	disconnect

continue

while boolean_expression 
		statement 
	break 
		statement 
continue

create database

create [temporary] database database_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}]

create default

create default [owner.]default_name 
	as constant_expression

create existing table

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

create function (SQLJ)

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 
		...] ] ) ] '

create index

create [unique] [clustered | nonclustered]
		index index_name 
	on [[database.]owner.]table_name 
		(column_name [asc | desc]
			[, column_name [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]

create plan

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

create procedure

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}

create procedure (SQLJ)

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

create proxy_table

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

create role

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

create rule

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

create schema

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

create table

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 }]
		[on segment_name]
		| references [[database .]owner .]ref_table
			[(ref_column )]
		| check (search_condition)}]}...
	| [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}...])]
		| 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 ]
	[ [ external table ] at pathname ]

create trigger

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

Or, using the if update clause:

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

create view

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

dbcc

dbcc addtempdb( dbid | databbase_name )

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

dbcc checkcatalog [(database_name)]

dbcc checkdb [(database_name [, skip_ncindex])]

dbcc checkstorage [(database_name)]

dbcc checktable({table_name | table_id}[, skip_ncindex])

dbcc checkverify [(database_name)]

dbcc complete_xact (xid, {"commit" | "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
	[, {full | optimized | fast | null}
	[, fix | nofix]])

dbcc pravailabletempdbs

dbcc rebuild_text (table [, column [, text_page_number]])

dbcc reindex ({table_name | table_id})

dbcc tablealloc ({table_name | table_id}
	[, {full | optimized | fast | null} 
	[, fix | nofix]])|

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

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

deallocate cursor

deallocate cursor cursor_name

declare

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

declare cursor

declare cursor_name cursor
	for select_statement
	[for {read only | update [of column_name_list]}]

delete

delete [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 [readpast] 
			[(index {index_name | table_name }
			[ prefetch size ][lru|mru])]}
	[, [[database.]owner.]{view_name [readpast]|
		table_name [readpast] 
			[(index {index_name | table_name }
			[ prefetch size ][lru|mru])]} ...] 
	[where search_conditions] ] 
	[plan "abstract plan"]

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

delete statistics

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

disk init

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

disk mirror

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

disk refit

disk refit

disk reinit

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

disk remirror

disk remirror 
	name = "device_name"

disk resize

disk resize
	name = “device_name”,
	size = additional_space

disk unmirror

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

drop database

drop database database_name [, database_name] ...

drop default

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

drop function (SQLJ)

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

drop index

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

drop procedure

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

drop role

drop role role_name [with override]

drop rule

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

drop table

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

drop trigger

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

drop view

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

dump 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] 
	[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, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		retaindays = number_days,
		[noinit | init],
		notify = {client | operator_console}
		} ] 

dump transaction

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, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		retaindays = number_days,
		[noinit | init],
		notify = {client | operator_console}, 
		standby_access }]

To truncate the log without making a backup copy:

dump tran[saction] database_name  
	with truncate_only

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

dump tran[saction] database_name  
	with no_log 

To back 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, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		retaindays = number_days,
		[noinit | init],
		no_truncate, 
		notify = {client | operator_console}}]

execute

[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]...)

fetch

fetch cursor_name [ into fetch_target_list ]

goto label

label: 
	goto label

grant

To grant 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_name}
	[with grant option]

To grant permission to execute certain commands:

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

To grant a role to a user or a role:

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

To grant and revoke access on certain dbcc commands:

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

group by and having clauses

Start of select statement

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

[having search_conditions]

End of select statement

if...else

if logical_expression [plan "abstract plan"]
	statements

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

insert

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

kill

kill spid

load database

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,
		dumpvolume = volume_name, 
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		listonly [= full],
		headeronly, 
		notify = {client | operator_console}
		}]]

load transaction

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, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		listonly [= full],
		headeronly,
		notify = {client | operator_console}
		until_time = datetime}]]

lock table

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

mount

mount all from <manifest_file>

mount database all from <manifest_file> with listonly

nullif

nullif(expression, expression)

online database

online database database_name [for standby_access]

open

open cursor_name

order by clause

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

prepare transaction

prepare tran[saction]

print

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

quiesce database

quiesce database tag_name hold dbname [, dbname] ...
	[for external dump] [manifest_file]

or:

quiesce database tag_name release

raiserror

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

readtext

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

reconfigure

reconfigure

remove java

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

reorg

reorg reclaim_space tablename [indexname]
	[with {resume, time = no_of_minutes}]

reorg forwarded_rows tablename 
	[with {resume,time = no_of_minutes}]

reorg compact tablename [with {resume, time = no_of_minutes}]

reorg rebuild tablename [indexname]

return

return [integer_expression] [plan "abstract plan"]

revoke

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_name}
	[cascade]

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

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

To revoke a role from a user or another role:

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

To revoke access on some dbcc commands.

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

rollback

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

rollback trigger

rollback trigger [with raiserror_statement]

save transaction

save transaction savepoint_name 

select

select ::=
	select [ all | distinct ] 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
	[ lock {datarows | datapages | allpages } ]
	[ with into_option [, into_option] ...]

	into_option ::= 
		| max_rows_per_page = num_rows
		| exp_row_size = num_bytes
		| reservepagegap = num_pages
		| identity_gap = gap 
		[existing table table_name]
		[[external type] at “path_name”
		[column delimiter delimiter]]

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"

set

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, procid, 
	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 explicit_transaction_required [true | false]

set fipsflagger {on | off}

set flushmessage {on | off}

set forceplan {on | off}

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

set jtc {on | off}

set lock { wait [ numsecs ] | nowait } 

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

set parallel_degree number 

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

set plan exists check {on | off}

set plan replace {on | off}

set prefetch [on|off]

set process_limit_action {abort | quiet | warning}

set proxy login_name

set quoted_identifier {on | off}

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 sort_merge {on | off}

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

set statistics simulate { on | off }

set strict_dtm_enforcement {on | off}

set string_rtruncation {on | off}

set table count number

set textsize {number}

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

set transactional_rpc {on | off}

setuser

setuser ["user_name"]

shutdown

shutdown [srvname ] [with {wait | nowait}]

truncate table

truncate table [[database.]owner.]table_name 

union operator

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

unmount

unmount database <dbname list> to <manifest_file>

update

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)}]...

	[from [[database.]owner.]{view_name [readpast]|
		table_name [readpast] 
			[(index {index_name | table_name}
			[ prefetch size ][lru|mru])]}
		[,[[database.]owner.]{view_name [readpast]|
		table_name [readpast] 
			[(index {index_name | table_name }
			[ prefetch size ][lru|mru])]}]
	...]
	[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

update all statistics

update all statistics table_name

update partition statistics

update partition statistics table_name [partition_number]

update statistics

update statistics table_name [ [index_name] | [( column_list ) ] ]
	[using step values]
	[with consumers = consumers ]

update index statistics table_name [index_name] 
	[using step values]
	[with consumers = consumers ]

use

use database_name 

waitfor

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

where clause

Search conditions immediately follow the keyword where in a select, insert, update, or delete statement. If you use more than one search condition in a single statement, connect the conditions with and or or.

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

where [not] time_period1 overlaps time_period2

while

while logical_expression [plan "abstract plan"]
	statement

writetext

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

---

Copyright © 2003. Sybase Inc. All rights reserved.

View this book as PDF