List of SQL Statements and Purposes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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

Moves a hash index from one map to another or changes the colocation for a sparse map.

ALTER JOIN INDEX

Moves a join index from one map to another or changes the colocation for a sparse map.

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 (Map and Colocation Form)

Moves a table from one map to another. You can also change the colocation name for a sparse map.

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

Starts an explicit concurrent isolated load operation on a load isolated (LDI) table. You can perform concurrent read operations on committed rows while the table is being loaded.

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.

CHECKPOINT ISOLATED LOADING

Sets a checkpoint for the explicit isolated load operation and commits the data that has been loaded up to that point.

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

Creates 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 FOREIGN TABLE Foreign tables enable Vantage to access data in external object storage, such as semi-structured and unstructured data in Amazon S3, Azure Blob storage, and Google Cloud Storage. In-database integration of this data allows data scientists and analysts to read and process this data with Vantage, using standard SQL. You can join external data to relational data in Vantage, and process it using built-in Vantage analytics and functions.
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 an SQL UDF.

CREATE FUNCTION MAPPING Creates a function mapping.
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 MAP

Creates a sparse map.

CREATE METHOD

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

CREATE ORDERING Creates 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 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 storage_format SCHEMA

Creates a schema for a specific storage format of the DATASET type.

CREATE TABLE and CREATE TABLE … AS

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 transform groups for importing UDT data from a client system to Vantage and exporting UDT data from Vantage to a client system. You can use CREATE TRANSFORM to add transform groups in addition to the existing transform groups.

CREATE TRIGGER

Creates a new trigger definition.

CREATE TYPE (ARRAY/VARRAY Form)

Creates a user-defined ARRAY or VARRAY data type that is constructed from a predefined Vantage 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 Vantage data type.
CREATE TYPE (Structured Form)

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

CREATE USER

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

CREATE VIEW

Creates 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 have 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 Data Dictionary and from the containing database or user.

DROP FUNCTION

Drops the definition of the specified function or specific function from the Data Dictionary and from the containing database or user.

DROP FUNCTION MAPPING

Drops a function mapping.

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 MAP

Drop a contiguous or sparse map.

DROP ORDERING

Drops the ordering definition for a UDT.

DROP PROCEDURE

Drops the definition for the specified procedure from the Data Dictionary and from the containing database or user.

DROP PROFILE

Drops a specified profile.

DROP ROLE

Drops a specified role.

DROP storage_format SCHEMA

Drops a schema.

DROP STATISTICS (Optimizer Form)

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

DROP TABLE

Drops the definition for a specified table from the Data Dictionary and from the containing database or user, depending on the keyword specified.

DROP TRANSFORM

Drop transform groups for a specified UDT.

DROP TRIGGER

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

DROP TYPE

Drops the definition for a specified UDT.

DROP USER

Drops the definition for an empty user from the Data Dictionary.

DROP VIEW

Drops the definition of a specified view from the Data Dictionary.

DROP ZONE

Removes a secure zone.

END ISOLATED LOADING

Ends the explicit concurrent isolated load operation for the specified load group value.

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.

FLUSH QUERY LOGGING

Flushes one, several, or all DBQL caches or 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 a specified database.

HELP ERROR TABLE

Displays the attributes for a 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 a specified query capture database (QCD) for a specified table.

HELP storage_format SCHEMA

Displays the name of a schema, the storage format of the DATASET type associated with the schema, and the length of the schema. The length is listed in UNICODE characters.

HELP TABLE

Displays the attributes for a specified base data table.

HELP TRANSFORM

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

HELP TRIGGER

Displays the attributes for a 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.

INCREMENTAL RESTORE ALLOW WRITE FOR object_list Enables read and write access for the databases, users, or tables you specify after an incremental restore. Incremental restore sets tables to read-only access.
LOGGING INCREMENTAL ARCHIVE OFF FOR object_list Disables incremental restore for databases and tables you specify.
LOGGING INCREMENTAL ARCHIVE ON FOR object_list Enables incremental restore for databases and tables you specify.
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.

REPLACE CAST

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 FUNCTION MAPPING Replaces the definition of an existing function mapping or, if the specified function mapping does not exist, creates a new function mapping by that name.
REPLACE MACRO

REPLACE MACRO redefines an existing macro. If the specified macro does not exist, REPLACE MACRO creates a new macro with that name.

REPLACE METHOD

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

Creates a rule or replaces the current rule with the rule you specify.

REPLACE RECURSIVE VIEW

Creates or replaces a recursive view definition.

REPLACE TRANSFORM Creates or replaces transform groups for importing UDT data from a client system to Vantage and exporting UDT data from Vantage to a client system. You can use CREATE TRANSFORM to add transform groups in addition to the existing transform groups. REPLACE TRANSFORM drops all of the existing transform groups and adds the transform groups you specify.
REPLACE TRIGGER

Creates a new trigger or replaces a 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 CHARACTER SET UNICODE PASS THROUGH

Enables or disables Unicode Pass Through processing for the session. Pass Through Characters (PTCs) include the Unicode characters that are not currently supported and character codes reserved for future use.

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

Sets the session response for dot notation query results that return a list of values.

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.

SET TRANSFORM GROUP FOR TYPE

Sets the active transform group for Complex Data Types (CDTs) that have multiple transform groups.

SHOW FUNCTION MAPPING

Displays the SQL data definition text for the function mapping.

SHOW MAP

Returns the equivalent CREATE MAP statement in SQL or, optionally, XML.

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.

SHOW TABLE Displays the most recent SQL create text.