Commands

SAP^® Adaptive Server^® Enterprise commands, clauses, and other elements are used to construct a Transact-SQL^® statement.

• alter database
  Increases the amount of space allocated to a database, as well as to the modified pages section of an archived database. Alters one or more database-wide properties such as data manipulation language (DML) logging level, defaults for compression, in-row large object (LOB) storage, and so on.
• alter encryption key
  Changes the current password, adds and drops a key copy, regenerates an encryption key.
• alter login
  Changes the attributes of a login account.
• alter index
  Renames indexes in base or global temporary tables and foreign key role names of indexes and foreign keys explicitly created by a user.
• alter login profile
  Changes the attributes of a login profile.
• alter precomputed result set
  Alters the properties or policies of a precomputed result set.
• alter...modify owner
  Transfers the ownership of database objects from one owner to another.
• 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. Also locks and unlocks roles.
• alter table
  Makes changes to existing tables.
• alter thread pool
  Alters a thread pool.
• begin...end
  Encloses a series of SQL statements so that control-of-flow language, such as if...else, can affect the performance of the entire group.
• begin transaction
  Marks the starting point of a user-defined transaction.
• break
  Causes an exit from a while loop. break is often activated by an if test.
• checkpoint
  Writes all dirty pages (pages that have been updated since they were last written) to the database device.
• close
  Deactivates a cursor.
• commit
  Marks the ending point of a user-defined transaction.
• compute Clause
  Generates summary values that appear as additional rows in the query results.
• connect to...disconnect
  (Component Integration Services only) Connects to the specified server and disconnects the connected server.
• continue
  Restarts the while loop. continue is often activated by an if test.
• create archive database
  Creates an archive database.
• create database
  Creates a new database.
• 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 encryption key
  Creates encryption keys. All the information related to keys and encryption is encapsulated by create encryption key, which allows you to specify the encryption algorithm and key size, the key’s default property, an optional user-specified password to encrypt the key, as well as the use of an initialization vector or padding during the encryption process.
• 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 function
  Creates a user-defined function, which is a saved Transact-SQL routine that returns a specified value.
• 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 index
  Creates an index on one or more computed or noncomputed columns in a table. Creates partitioned indexes. Allows computed columns, like ordinary columns, to be index keys, and creates function-based indexes. A function-based index has one or more expressions as its index key.
• create login
  Creates a login account; specifies a password, a login profile for the account, and user-supplied parameters to be assigned to the account.
• create login profile
  Creates a login profile with specified attributes.
• create plan
  Creates an abstract plan.
• create precomputed result set
  Creates precomputed result sets and the policies required to maintain them.
• create procedure
  Creates or replaces a stored procedure or an extended stored procedure (ESP) that can take one or more user-supplied parameters.
• create procedure (SQLJ)
  Creates or replaces a SQLJ stored procedure by adding a SQL wrapper to a Java static method. Accepts user-supplied parameters and return result sets and output parameters.
• create proxy_table
  (Component Integration Services only) Creates a proxy table without specifying a column list. CIS derives the column list from the metadata it obtains from the remote table.
• 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 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 schema
  Creates a new collection of tables, views, and permissions for a database user.
• create service
  Wraps the supplied SQL statement in a stored procedure with the specified name and parameters.
• create table
  Creates new tables and optional integrity constraints, defines computed columns and table, row, index, and partition compression levels. Defines encrypted columns, decrypt defaults, and partition properties. Removes residual data from deletions.
• create thread pool
  Creates a user-defined thread pool.
• create trigger
  Creates one or more new triggers, or re-creates an existing trigger. A trigger is a type of stored procedure that is often used to enforce integrity constraints, executing automatically when a user attempts a specified data modification statement on a specified table.
• create view
  Creates or replaces a view, which is an alternative way to look at the data in one or more tables.
• dbcc
  Database consistency checker (dbcc) checks the logical and physical consistency of a database and provides statistics, planning, and repair functionality.
• deallocate cursor
  Makes a cursor inaccessible and releases all memory resources that are committed to that cursor.
• deallocate locator
  Deletes a large object (LOB) stored in memory and invalidates its LOB locator.
• declare
  Declares the name and type of local variables for a batch or procedure.
• declare cursor
  Defines a cursor, by associating a select statement with a cursor name. You can use declare cursor with an archive database.
• delete
  Removes rows from a table.
• delete statistics
  Removes statistics from the sysstatistics system table.
• disk init
  Makes a physical device or file usable by the SAP ASE server.
• disk mirror
  Creates a software mirror that immediately takes over when the primary device fails.
• disk refit
  Rebuilds the master database’s sysusages and sysdatabases system tables from information contained in sysdevices.
• disk reinit
  Rebuilds the master database’s sysdevices system table. Use disk reinit as part of the procedure to restore the master database.
• disk remirror
  Restarts disk mirroring after it is stopped by failure of a mirrored device, or temporarily disabled by the disk unmirror command.
• disk resize
  Dynamically increases the size of the device used by the SAP ASE server.
• disk unmirror
  Suspends disk mirroring initiated with the disk mirror command to allow hardware maintenance or the changing of a hardware device.
• drop database
  Removes one or more databases, including archive databases, from the SAP ASE server.
• drop default
  Removes a user-defined default.
• drop encryption key
  Allows key owners to drop the named encryption key, including database encryption keys used for fully encrypted databases.
• drop function
  Removes one or more user-defined functions from the current database.
• drop function (SQLJ)
  Removes a SQLJ function.
• drop index
  Removes an index from a table in the current database.
• drop login
  Drops a login account or list of accounts.
• drop login profile
  Drops a login profile or list of login profiles.
• drop precomputed result set
  Drops a precomputed result set.
• drop procedure
  Removes a procedure.
• drop role
  Drops a user-defined role.
• drop rule
  Removes a user-defined rule.
• drop service
  The drop service command removes a user-defined Web service from the current database. Both the metadata and the corresponding stored procedure are removed.
• drop table
  Removes a table definition and all of its data, indexes, partition properties, triggers, encryption properties, and permissions from the database.
• drop thread pool
  Drops a user-defined pool.
• drop trigger
  Removes a trigger.
• drop view
  Removes one or more views from the current database.
• dump configuration
  Creates a backup of the SAP ASE configuration files into a specified dump directory. The copy is created by the SAP ASE server, not the Backup Server.
• 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. Dumps and loads are performed through Backup Server.
• dump transaction
  Makes a copy of a transaction log, and removes the inactive portion of the log, if the dump transaction command is not running concurrently with another dump database.
• execute
  Runs a procedure or dynamically executes Transact-SQL commands.
• fetch
  Returns a row or a set of rows from a cursor result set.
• goto label
  Branches to a user-defined label.
• grant
  Assigns permissions to individual users, groups of users, and roles.
• grant role
  Grants a role to the specified logins, users, or system- or user-defined roles.
• 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. group by is typically used with aggregates to specify how to group the unaggregated columns of a select query. having clauses are applied to these groups.
• if...else
  Imposes conditions on the execution of a SQL statement.
• insert
  Adds new rows to a table or view.
• kill
  Kills a process.
• load database
  Loads a backup copy of a user database, including its transaction log, that was created with dump database, as well as materializes archive databases that have been loaded with a database dump.
• load transaction
  Loads a backup copy of the transaction log that was created with dump transaction.
• lock table
  Explicitly locks a table within a transaction.
• merge
  Transfers rows from a source table into a target table.
• mount
  Attaches a database to a destination or secondary SAP ASE server.
• 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 SAP ASE; brings a database online after loading a transaction log dumped with the for standby_access option. You can also use online database to bring an archive database online.
• open
  Opens a cursor for processing.
• order by clause
  Returns query results in the specified columns in sorted order.
• prepare transaction
  Used by DB-Library in a two-phase commit application to see if a server is prepared to commit a transaction.
• print
  Prints a user-defined message on the user’s screen.
• quiesce database
  Suspends and resumes updates to a specified list of databases.
• 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.
• readtext
  Reads text, unitext, and image values, starting from a specified offset and reading a specified number of bytes or characters.
• reconfigure
  Allows existing scripts to run without modification (currently has no effect).
• refresh precomputed result set
  Refreshes the specified precomputed result set.
• remove java
  Removes one or more Java-SQL classes, packages, or JARs from a database, when Java classes are installed in the database.
• reorg
  Reclaims unused space on pages, removes row forwarding, or rewrites all rows in the table to new pages, depending on the option used.
• return
  Exits from a batch or procedure unconditionally and provides an optional return status. Statements following return are not executed.
• revoke
  Revokes permissions from users, roles, or groups.
• revoke role
  Revokes a role from a group, login, login profile, or role:
• rollback
  Rolls back a user-defined transaction to the named savepoint in the transaction or to the beginning of the transaction.
• 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.
• save transaction
  Sets a savepoint within a transaction.
• select
  Retrieves rows from database objects.
• set
  Sets SAP ASE query-processing options for the duration of the user’s work session; sets some options inside a trigger or stored procedure.
• setuser
  Allows a database owner to impersonate another user.
• shutdown
  Shuts down the SAP ASE server from which the command is issued, its local Backup Server, or a remote Backup Server.
• transfer table
  Initiates an incremental table transfer.
• truncate lob
  Truncates an LOB to a specified length.
• truncate precomputed result set
  Truncates the data in a precomputed result set.
• truncate table
  Removes all rows from a table or partition.
• union operator
  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.
• unmount
  Shuts down the database and drops it from the SAP ASE server. The devices are also deactivated and dropped.
• update
  Changes data in existing rows, either by adding data or by modifying existing data.
• update all statistics
  Updates all statistics information for a given table, including histograms on all columns, regardless of whether they are indexed. You can run update all statistics on a single data partition.
• update index statistics
  Updates the statistics for all columns in an index.
• update statistics
  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 table statistics
  Updates statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on. Does not affect column statistics stored in sysstatistics.
• use
  Specifies the database with which you want to work.
• waitfor
  Specifies a specific time, a time interval, or an event for the execution of a statement block, stored procedure, or transaction.
• where clause
  Sets the search conditions in a select, insert, update, or delete statement.
• while
  Sets a condition for the repeated execution of a statement or statement block. The statements are executed repeatedly, as long as the specified condition is true.
• writetext
  Permits minimally logged, interactive updating of an existing text, unitext or image column.