15.10 - List of SQL Statements and Purposes - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

This table alphabetically lists SQL statements and their purpose.

Statement Purpose
ALTER CONSTRAINT

Modifies the definition of an existing row-level security constraint object.

ALTER FUNCTION
Performs either or both of the following functions.
  • Controls whether an existing function can run in protected mode as a separate process or in unprotected mode as part of the database.
  • Recompiles C or C++ functions or relinks Java functions and redistributes them.
Java UDFs must always run in protected mode, so you cannot use this statement to change their protection mode.
ALTER METHOD
Performs either or both of the following functions.
  • Controls whether an existing method can run in protected mode as a separate process or in non-protected mode as part of the database.
  • Recompiles or relinks the method and redistributes it.
ALTER PROCEDURE (External Form)
Recompiles an existing external procedure and allows changes in the following compile time attributes of the procedure.
  • Generate a new library for the recompiled procedure or not.
  • Toggle the protection mode between protected and unprotected states.
  • Change the creation time zone.
ALTER PROCEDURE (SQL Form)
Recompiles an existing SQL procedure and allows changes in the following compile time attributes of the procedure.
  • SPL option
  • WARNING option
  • AT TIME ZONE option
ALTER TABLE

Add one or more columns to a table or global temporary table, add or change attributes and options, including partitioning, constraints, and compression. You can also drop columns, change a join index, or revalidate a table.

ALTER TABLE TO CURRENT

Reconciles the row partitioning for a table or uncompressed join index to a newly resolved date or timestamp when its partitioning is based on the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions.

ALTER TRIGGER

Enables or disables a trigger or changes its creation timestamp.

ALTER TYPE
Performs any of the following operations for a UDT.
  • Add a new attribute to a structured UDT definition.
  • Drop an existing attribute from a structured UDT definition.
  • Add a method to a distinct or structured UDT definition.
  • Drop a method from a distinct or structured UDT definition.
  • Recompile the source code for a distinct, structured, ARRAY, or VARRAY type definition.
ALTER ZONE

Add a database or user as the root to a secure zone or remove the root database or user from a secure zone.

BEGIN LOGGING

Starts the auditing of SQL requests that attempt to access data.

BEGIN QUERY CAPTURE

Starts the logging of database request information, including some database object creation and drop information.

BEGIN QUERY LOGGING

Collects demographic data for one or more columns, computes a statistical profile of the collected data, stores the synopsis in DBC.StatsTbl in the data dictionary, and optionally copies the statistics for one or more columns to a duplicate target table.

COLLECT STATISTICS (Optimizer Form)

Collects demographic data for one or more columns, computes a statistical profile of the collected data, stores the synopsis in DBC.StatsTbl in the data dictionary, and optionally copies the statistics for one or more columns to a duplicate target table.

COMMENT (Comment Placing Form)

Creates a user-defined description of a user-defined database object or definition in the data dictionary.

CREATE AUTHORIZATION

Creates or replaces an authorization object.

CREATE CAST and REPLACE CAST

Creates or replaces a cast operation for a UDT.

CREATE CONSTRAINT

Creates the SQL row-level security constraint definition and associates it with specific UDFs to enforce that constraint.

CREATE DATABASE

Creates a database in which other database objects can be created.

CREATE ERROR TABLE

Defines the name and containing database of a new error table and specifies the name of the associated data table.

CREATE FUNCTION (External Form)

Compiles and installs an external UDF and creates or replaces the SQL function definition used to invoke that UDF.

CREATE FUNCTION (Table Form)

Creates a table function definition.

CREATE FUNCTION (SQL Form)

Creates or replaces an SQL UDF.

CREATE GLOBAL TEMPORARY TRACE TABLE

Creates a global temporary trace table to support the UDF- and external SQL procedure-related trace option of the SET SESSION statement. See SET SESSION FUNCTION TRACE.

CREATE GLOP SET

Creates the definition of a global persistent memory set.

CREATE HASH INDEX

Creates a hash index on a base table.

CREATE INDEX

Creates a secondary index on an existing data table or join index.

CREATE JOIN INDEX

Creates a join index for one or multiple tables, optionally with aggregation.

CREATE MACRO

Defines a set of statements that are frequently used or that perform a complex operation. The statements in the macro body are submitted when the macro is invoked by a subsequent EXECUTE statement.

CREATE METHOD

Defines the body of a method that is associated with a user-defined data type.

CREATE ORDERING

Creates or replaces a map ordering routine used to compare UDT values.

CREATE PROCEDURE (External Form)

Compiles and installs an external SQL procedure routine and creates or replaces the SQL definition used to invoke the procedure.

CREATE PROCEDURE (SQL Form)

Directs the SQL procedure compiler to create or replace a procedure from the SQL statements in the remainder of the source text and creates the SQL definition used to invoke the procedure.

CREATE PROFILE

Creates a profile that defines a set of parameters. You can then assign the profile to multiple users.

CREATE RECURSIVE VIEW

Creates or replaces a recursive view definition.

CREATE ROLE

Creates a role for managing user access privileges on database objects. A role is a shell database object to which sets of privileges can be granted using GRANT requests.

CREATE TABLE

Defines the column names, column data types and attributes, primary and secondary indexes, column- and table-constraints, partitioning, and other attributes of a new table. The CREATE TABLE AS form copies column definitions to a new table. Optionally, data and statistics are copied to the new table.

CREATE TABLE (Queue Table Form)

Creates a queue table.

CREATE TRANSFORM

Creates or replaces a transform group to handle the import and export of UDT data from a client system to Teradata Database and from Teradata Database to a client system.

CREATE TRIGGER

Creates or replaces a new trigger definition.

CREATE TYPE (ARRAY/VARRAY Form)

Creates a user-defined ARRAY or VARRAY data type that is constructed from a predefined Teradata Database data type, a distinct UDT data type, a structured UDT data type, or an internal UDT data type.

CREATE TYPE (Distinct Form)

Creates a user-defined data type that is constructed directly from a predefined Teradata Database data type.

CREATE TYPE (Structured Form)

Creates the body of a structured user-defined data type.

CREATE USER

Defines a permanent Teradata Database user, in which other database objects may be created, with a mandatory permanent space allocation and a mandatory password plus optional attributes.

CREATE VIEW

Creates or replaces a view on a set of tables or views or both.

CREATE ZONE

Defines a secure zone. You can also specify an existing database or user as the zone root.

DATABASE

Establishes a new default database for the current session for SQL requests that do not require fully-qualified table, view, or macro names.

DELETE DATABASE

Deletes all data tables, views, triggers, SQL procedures, macros, and user-installed files (UIFs) from a database.

DELETE USER

Deletes all data tables, views, triggers, SQL procedures, macros, and user-installed files (UIFs) from a user.

DROP AUTHORIZATION

Drops an authorization object.

DROP CAST

Drops a cast definition for a UDT from the data dictionary.

DROP CONSTRAINT

Drops the definition of a row-level security constraint object from the data dictionary.

DROP DATABASE

Drops the definition for an empty database from the data dictionary.

DROP ERROR TABLE

Deletes the specified error table object definition from the dictionary and drops it from its containing database or user.

DROP FUNCTION

Drops the definition of the specified function or specific function from the data dictionary and drops it from its containing database or user.

DROP GLOP SET

Drops the definition of the specified GLOP set from the data dictionary.

DROP HASH INDEX

Drops a hash index on a table.

DROP INDEX

Drops a secondary index on a table or join index.

DROP JOIN INDEX

Drops the join index.

DROP MACRO

Drops the definition for the specified macro from the dictionary.

DROP ORDERING

Drops the ordering definition for a UDT.

DROP PROCEDURE

Drops the definition for the specified procedure from the data dictionary and drops the procedure from its containing database or user.

DROP PROFILE

Drops the specified profile.

DROP ROLE

Drops the specified role.

DROP STATISTICS (Optimizer Form)

Drops the statistical data that was collected for the specified columns of a table, hash index, or join index by a COLLECT STATISTICS (Optimizer Form) request. The only way to drop the SUMMARY statistics for a database object is to drop all statistics on that object.

DROP TABLE

Drops the definition for the specified table from the data dictionary and drops the object from its containing database or user, depending on the keyword specified.

DROP TRANSFORM

Drops the transform group for a specified UDT.

DROP TRIGGER

Drops the definition for the specified trigger from its subject table.

DROP TYPE

Drops the definition for the specified UDT.

DROP USER

Drops the definition for an empty user from the data dictionary.

DROP VIEW

Drops the definition of the specified view from the data dictionary.

DROP ZONE

Remove a secure zone.

END LOGGING

Ends the auditing of SQL requests that was started with a BEGIN LOGGING request.

END QUERY CAPTURE

Stops the capture of SQL requests initiated by a BEGIN QUERY CAPTURE request for the session.

END QUERY LOGGING

Stops the logging of SQL requests initiated by a BEGIN QUERY LOGGING request and commits the query log cache. See “BEGIN QUERY LOGGING.”

FLUSH QUERY LOGGING

To flush one, several, or all database query log caches or Teradata workload management caches to disk.

HELP CAST

Returns the available cast operations for the specified UDT.

HELP COLUMN

Displays the attributes of a column, including whether it is a single-column primary or secondary index and, if so, whether it is unique.

HELP CONSTRAINT

Displays the attributes for a specific named constraint on a table. Use the SHOW TABLE statement to obtain unnamed constraint information. See “SHOW object.”

HELP DATABASE

Displays the attributes, sorted by object name, for all tables, views, join indexes, hash indexes, SQL procedures, user-defined functions, and macros contained by the specified database.

HELP ERROR TABLE

Displays the attributes for the specified error table.

HELP FUNCTION

Reports the specific function name, list of parameters, the data types of the parameters, whether the function is used to compress or decompress character or graphic data, and any comments associated with the parameters for SQL, scalar, aggregate, and table functions.

HELP HASH INDEX

Displays the data types of the columns defined by a particular hash index.

HELP INDEX

Displays the attributes for the primary and secondary indexes defined for a base data table, hash index, or join index.

HELP JOIN INDEX

Displays the attributes of the columns defined by a particular join index.

HELP MACRO

Displays the attributes for the specified macro.

HELP METHOD

Displays the parameter list of the specified method.

HELP ONLINE

Displays syntax help for any SQL statement or client utility command.

HELP PROCEDURE

Displays the attribute and format parameters for each parameter of a procedure or just the creation time attributes for the specified procedure.

HELP SESSION

Displays attribute information for the user of the current session or just the row-level constraint attribute information for the user of the current session.

HELP STATISTICS (Optimizer Form)

Displays the attribute and format parameters for each parameter of a procedure or just the creation time attributes for the specified procedure.

HELP STATISTICS (QCD Form)

Displays summary or detailed attributes for the statistics that have been collected in the TableStatistics table of the specified QCD database for the specified table.

HELP TABLE

Displays the attributes for the specified base data table.

HELP TRANSFORM

Reports the fromsql and tosql transform routines for the specified UDT or predefined data type.

HELP TRIGGER

Displays the attributes for the specified trigger.

HELP TYPE

Displays the attributes for a UDT and, optionally, all the attributes of a structured UDT or all the methods associated with the UDT.

HELP USER

Displays the attributes, sorted by object name, for all tables, views, join indexes, hash indexes, SQL procedures, user-defined functions, and macros contained by the specified user.

HELP VIEW

Displays the attributes for the specified view or recursive view.

HELP VOLATILE TABLE

Displays the attributes for the requested volatile table.

HELP ONLINE

Displays syntax help for any SQL statement or client utility command.

MODIFY DATABASE

Changes the parameters for the specified database.

MODIFY PROFILE

Changes the parameters for the specified profile.

MODIFY USER

Changes parameters assigned to the specified user.

RENAME FUNCTION (External Form)

Renames either the overloaded calling function name or specific function name for an external function.

RENAME FUNCTION (SQL Form)

Renames either the overloaded calling function name or specific function name for an SQL function.

RENAME MACRO

Renames an existing macro.

RENAME PROCEDURE

Renames an existing SQL procedure.

RENAME TABLE

Renames an existing table.

RENAME TRIGGER

Renames an existing trigger.

RENAME VIEW

Renames an existing view.

REPLACE AUTHORIZATION

Creates or replaces an authorization object.

CREATE CAST and REPLACE CAST

Creates or replaces a cast operation for a UDT.

REPLACE FUNCTION (External Form)

Compiles and installs an external UDF and creates or replaces the SQL function definition used to invoke that UDF.

REPLACE FUNCTION (SQL Form)

Creates or replaces an SQL UDF.

REPLACE METHOD

Replaces an existing method definition.

REPLACE ORDERING

Creates or replaces a map ordering routine used to compare UDT values.

REPLACE PROCEDURE (External Form)

Compiles and installs an external SQL procedure routine and creates or replaces the SQL definition used to invoke the procedure.

REPLACE PROCEDURE (SQL Form)

Directs the SQL procedure compiler to create or replace a procedure from the SQL statements in the remainder of the source text and creates the SQL definition used to invoke the procedure.

REPLACE QUERY LOGGING

Replaces the current rule with rule you specify.

REPLACE RECURSIVE VIEW

Creates or replaces a recursive view definition.

REPLACE TRANSFORM

Creates or replaces a transform group to handle the import and export of UDT data from a client system to Teradata Database and from Teradata Database to a client system.

REPLACE TRIGGER

Creates or replaces a new trigger definition.

REPLACE VIEW

Creates or replaces a view on a set of tables or views or both.

SET QUERY_BAND

Sets or removes a query band for the current session or transaction.

SET ROLE

Sets the current role for a session. It does not distinguish between directory- and database-managed roles.

SET SESSION

Allows the setting of various session parameters for the entire session or individual requests within the session.

SET SESSION ACCOUNT

Dynamically changes your account or account priorities for the duration of a session or for one SQL request only.

SET SESSION CALENDAR

Sets the default calendar for the session to a system-defined calendar.

SET SESSION COLLATION

Changes the collation sequence for the current session.

SET SESSION CONSTRAINT

Overrides the default constraints assigned to a user for the current session.

SET SESSION DATABASE

Changes the default database for the session.

SET SESSION DATEFORM

Changes the default DATE format in field mode and the default format for importing and exporting DATE values for the session.

SET SESSION DEBUG FUNCTION

Identifies the UDF or stored procedure to be run in debug mode the next time the UDF is invoked.

SET SESSION FOR ISOLATED LOADING

Enables or disables isolated loading for DML operations for the session.

SET SESSION FUNCTION TRACE

Enables function trace output for debugging external user-defined functions and external SQL procedures.

SET SESSION JSON IGNORE ERRORS

Enables or disables the validation of JSON data on INSERT operations.

SET SESSION SEARCHUIFDBPATH

Sets the database search path for the SCRIPT execution in the SessionTbl.SearchUIFDBPath column.

SET SESSION TRANSACTION ISOLATION LEVEL

Changes the default transaction isolation level read-only semantics for the current session.

SET TIME ZONE

Changes the default time zone displacement for a session.

SHOW object

For tables, macros and views, displays the SQL data definition text for the original create text from DBC.TVM.RequestText.

SHOW QUERY LOGGING

Returns the query logging rule set applied to the specified user, database, user:account set, application set, or all users from the rules cache or from DBC.DBQLRuleTbl.

SHOW request

Displays the DDL for all database objects referenced by a specified DML request.

SHOW STATISTICS

Reports the SQL text for the Optimizer and QCD forms of COLLECT STATISTICS requests that collected the statistics and optionally reports the detailed or summary statistics.