Commands

This section provides brief descriptions and syntax for Adaptive Server commands. See Reference Manual: Commands for complete descriptions, examples, and usage information.

alter database

Increases the amount of space allocated to a 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

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.

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

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.

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)]
			[match full]
		| 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}...])]
		[match full]
	| 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}
		| { enable | disable } trigger
	| lock {allpages | datarows | datapages } }
	| with exp_row_size=num_bytes
	| partition number_of_partitions
	| unpartition
	| partition_clause
	| add_partition_clause

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

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

begin...end

Encloses a series of SQL statements so that control-of-flow language, such as if...else, can affect the performance of the whole group.

begin 
	statement block
end

begin transaction

Marks the starting point of a user-defined transaction.

begin tran[saction] [transaction_name]

break

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

while logical_expression 
	statement 
break 
	statement 
continue

checkpoint

Writes all dirty pages (pages that have been updated since they were last written) to the database device.

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

close

Deactivates a cursor.

close cursor_name

commit

Marks the ending point of a user-defined transaction.

commit [tran | transaction | work] [transaction_name]

compute clause

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

connect to...disconnect

Component Integration Services only – connects to the specified server and disconnects the connected server.

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

Creates a passthru to a different server:

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, and does not use CIS:

connect using connect_string

continue

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

while boolean_expression 
		statement 
	break 
		statement 
continue

create database

Creates a new 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

Specifies a value to insert in a column (or in all columns of a user-defined datatype) if no value is explicitly supplied at insert time.

create default [owner.]default_name 
	as constant_expression

create existing table

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} ] at pathname 
	[column delimiter “string”]]

create function (SQLJ)

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

create index

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

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 ]

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

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

create plan

Creates an abstract plan.

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

create procedure

Creates a stored procedure or an extended stored procedure (ESP) 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}

create procedure (SQLJ)

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

create proxy_table

Component Integration Services only– Creates a proxy table without specifying a column list. Component Integration Services derives the column list from the metadata it obtains from the remote table.

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

create role

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. You can also associate a password with the role at the time that the role is created.

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

create rule

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

create schema

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

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

create table

Creates new tables and optional integrity constraints, defines computed columns when a table is created, 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 }]
		[on segment_name]
		| references [[database .]owner .]ref_table
			[(ref_column )]
			[match full]
			| check (search_condition)}]}
			[match full]...
	| [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) ... }
	[{, {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 ]

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

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

create trigger

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

Creates a view.

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

dbcc

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 { 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 
	[, object_name | object_type [, force | check])))

deallocate cursor

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

deallocate [cursor] cursor_name

declare

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

declare cursor

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

delete

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

Removes statistics from the sysstatistics system table.

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

disk init

Makes a physical device or file usable by Adaptive Server.

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

disk mirror

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

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

disk refit

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

disk refit

disk reinit

Rebuilds the master database’s sysdevices system table. Use disk reinit as part of the procedure to restore the master database.

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

disk remirror

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"

disk resize

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

disk resize
	name = “device_name”,
	size = additional_space

disk unmirror

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

drop database

Removes one or more databases from Adaptive Server.

drop database database_name [, database_name] ...

drop default

Removes a user-defined default.

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

drop function (SQLJ)

Removes a SQLJ function.

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

drop index

Removes an index from a table in the current database.

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

drop procedure

Removes a procedure.

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

drop role

Drops a user-defined role.

drop role role_name [with override]

drop rule

Removes a user-defined rule.

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

drop table

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

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

drop trigger

Removes a trigger.

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

drop view

Removes one or more views from the current database.

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

dump database

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

dump transaction

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

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

execute

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]

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

fetch

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 ]

goto label

Branches to a user-defined label.

label: 
	goto label

grant

Assigns permissions to individual users, groups of users, and roles. Assigns roles to users or system or user-defined roles. 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_list}
	[with grant option]

To grant permission to use built-in functions:

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

To grant permission to execute certain commands:

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

To grant access on certain dbcc commands:

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

To grant the default permissions for specific system tables:

grant default permissions on system tables

To grant a role to a user or a role:

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

To switch 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
	[restricted role role_list | all | system]

group by and having clauses

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

if...else

Imposes conditions on the execution of a SQL statement.

if logical_expression [plan "abstract plan"]
	statements

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

insert

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

kill

Kills a process.

kill spid with statusonly

load database

Loads a backup copy of a user database, including its transaction log, that was created with dump database. To make 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}
		}]]

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

load transaction

Loads a backup copy of the transaction log that was created with dump transaction. To make 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}
		}]]

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

lock table

Explicitly locks a table within a transaction.

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

mount

Attaches a database to a destination or secondary Adaptive Server. You cannot execute mount without first performing unmount on your database.

mount database all from <manifest_file> [with listonly]

online database

Marks a database available for public use after a normal load sequence; if needed, upgrades a loaded database to the current version of Adaptive Server; brings a database online after loading a transaction log dumped with the for standby_access option.

online database database_name [for standby_access]

open

Opens a cursor for processing.

open cursor_name

order by clause

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]

prepare transaction

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

prepare tran[saction]

print

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

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

quiesce database

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

quiesce database tag_name release

raiserror

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]

readtext

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

reconfigure

The reconfigure command currently has no effect; it is included to allow existing scripts to run without modification.

reconfigure

remove java

Removes one or more Java-SQL classes, packages, or JARs from a database.

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

reorg

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

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

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

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

return

Exits from a batch or procedure unconditionally and provides an optional return status. Statements following return are not executed.

return [integer_expression] [plan "abstract plan"]

revoke

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]

To revoke permission to select built-in functions:

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

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

To revoke a role from a user or another role:

revoke role {role_name [, role_list ...]} 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 }

To revoke the default permissions from public:

revoke default permissions on system tables

rollback

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]

rollback trigger

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]

save transaction

Sets a savepoint within a transaction.

save transaction savepoint_name 

select

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 
		[ { [ external table at ]
			‘server_name.[database].[owner].object_name’ 
			| external directory at ‘pathname’ 
			| external file at ‘pathname’ [column deliminter ‘string’ ] } ]
		[ on segment_name ]
		[ 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"

set

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 @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 delayed_commit { on | off | default }

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 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 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 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 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 statistics {io, subquerycache, time, plancost} {on | off}

set statistics simulate { on | off }

set strict_dtm_enforcement {on | off}

set string_rtruncation {on | off}

set textsize {number}

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

set transactional_rpc {on | off}

setuser

Allows a Database Owner to impersonate another user.

setuser ["user_name"]

shutdown

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

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

truncate table

Removes all rows from a table or partition.

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

union operator

Returns a single result set that combines the results of two or more queries.

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]

unmount

Shuts down the database and drops it from the Adaptive Server. Do not use this command without first reading the full description in Reference Manual: Commands.

unmount database dbname_list to manifest_file

update

Changes data in existing rows, either by adding data or by 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 [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

Updates all statistics information for a given table.

update all statistics table_name [ partition data_partition_name ]

update index statistics

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]

update statistics

Updates information about the distribution of key values in specified indexes, for all columns in an index, table, or partition.

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 ]

update table statistics

Updates statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on.

update table statistics table_name
	[ partition data_partition_name ]

use

Specifies the database with which you want to work.

use database_name 

waitfor

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 } 

where clause

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

while

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

while logical_expression [plan "abstract plan"]
	statement

writetext

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

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF