Chapter 1: Commands  alter database

Chapter 1: Commands

Overview

Table 1-1 provides a brief description of the commands in this chapter.

Table 1-1: Transact-SQL commands

Command

Description

alter database

Increases the amount of space allocated to a database.

alter role

Defines mutually exclusive relationships between roles, adds, drops, and changes passwords for roles.

alter table

Adds new columns; adds, changes, or drops constraints, changes constraints; partitions or unpartitions an existing table.Creates computed columns.

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

Specifies the server to which a passthrough connection is required.

continue

Causes the while loop to restart. continue is often activated by an if test.

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

Confirms that the current remote table information matches the information that is stored in column_list, and verifies the existence of the underlying object.

create function (SQLJ)

Creates a user-defined function by adding a SQL wrapper to a Java static method.

create index

Creates an index on one or more columns in a table. Creates a function-based index.

create plan

Creates an abstract query plan.

create procedure

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

create procedure (SQLJ)

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

create proxy_table

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 role

Creates a user-defined role.

create rule

Specifies the domain of acceptable values for a particular column or for any column of a user-defined datatype.

create schema

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

create table

Creates new tables and optional integrity constraints.Creates a table containing computed columns and function-based indexes.

create trigger

Creates a trigger, which is a type of stored procedure often used for enforcing integrity constraints. A trigger executes automatically when a user attempts a specified data modification statement on a specified table.

create view

Creates a view, which is an alternative way of looking at the data in one or more tables.

dbcc

Checks the logical and physical consistency of a database. Use dbcc regularly as a periodic check or if you suspect any damage.

deallocate cursor

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

declare

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

declare cursor

Defines a cursor.

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 Adaptive 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. Use disk refit after disk reinit as part of the procedure to restore the master database.

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

Reenables 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 database devices.

disk unmirror

Disables either the original device or its mirror, allowing hardware maintenance or the changing of a hardware device.

drop database

Removes one or more databases from an Adaptive Server.

drop default

Removes a user-defined default.

drop function (SQLJ)

Removes a SQLJ function.

drop index

Removes an index from a table in the current database.

drop procedure

Removes user-defined stored procedures.

drop role

Removes a user-defined role.

drop rule

Removes a user-defined rule.

drop table

Removes a table definition and all of its data, indexes, triggers, and permission specifications from the database.

drop trigger

Removes a trigger.

drop view

Removes one or more views from the current database.

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.

execute

Runs a system procedure, a user-defined stored procedure, or a dynamically constructed Transact-SQL command.

fetch

Returns a row or a set of rows from a cursor result set. In scrollable cursors, fetch orientation keywords specify the position of the row to fetch.

goto label

Branches to a user-defined label.

grant

Assigns permissions to users or to 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.

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.

load transaction

Loads a backup copy of the transaction log.

lock table

Explicitly locks a table within a transaction.

mount

Attaches the database to the destination or secondary Adaptive Server.

online database

Marks a database available for public use after a normal load sequence and, if needed, upgrades a loaded database and transaction log dumps to the current version of Adaptive Server.

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

Currently has no effect; included to allow existing scripts to run without modification. In earlier versions, you were required to execute reconfigure after sp_configure, to implement new configuration parameter settings.

remove java

Removes one or more Java-SQL classes, packages, or JARs from a database. Use when Java is enabled 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, optionally providing a return status. Statements following return are not executed.

revoke

Revokes permissions or roles from users or roles.

rollback

Rolls a user-defined transaction back to the last savepoint inside the transaction or to the beginning of the transaction.

rollback trigger

Rolls back the work done in a trigger, including the update 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 Adaptive Server query-processing options for the duration of the user’s work session. Can be used to set some options inside a trigger or stored procedure. Can also be used to activate or deactivate a role in the current session.

setuser

Allows a Database Owner to impersonate another user.

shutdown

Shuts down Adaptive Server or a Backup Server™. This command can be issued only by a System Administrator.

truncate table

Removes all rows from a table.

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 Adaptive Serve, and deactivates and drops devices.

update

Changes data in existing rows, either by adding data or by modifying existing data; updates all statistics information for a given table; updates information about the number of pages in each partition for a partitioned table; updates information about the distribution of key values in specified indexes.

update all statistics

Updates all statistics information for a given table.

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.

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. Statements execute repeatedly, as long as the specified condition is true.

writetext

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





Copyright © 2005. Sybase Inc. All rights reserved. alter database

View this book as PDF