SQL Command Reference (alphabetical) - Teradata VantageCloud Lake

This is a list of all DCL, DDL, and DML commands, in alphabetical order.

Command	Purpose
ABORT	Terminates the current transaction and rolls back its updates.
ALTER CONSTRAINT	Modifies the definition of an existing row-level security constraint object.
ALTER DATALAKE	Changes the connection and authentication information and attributes of an existing Datalake object. Cannot change the table format of the object.
ALTER FOREIGN TABLE	Changes the schema, attributes, or partitioning of a specified foreign table.
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 run in protected mode, so you cannot use this statement to change their protection mode.
ALTER JOIN INDEX	Moves a join index from one map to another or changes the colocation for a sparse map.
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, revalidate a table, or change an empty Block File System table to an Object File System table.
ALTER TABLE (Datalake Form)	Modifies an Open Table Format table in the OTF metadata.
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 (Storage Conversion Form)	Converts an empty Block File System table to an Object File System 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 TABLE REORGANIZE	Reorganizes an Object File System table for performance improvement. Combines small objects (typically produced by INSERT operations into the table) into a larger set of objects, thereby improving performance of future queries on the table.
ALTER TABLE SAVE \| UNSAVE	Keeps a version of an Object File System table past the retention period based on timestamp. ALTER TABLE UNSAVE allows a previously-saved version of the Object File System table to be removed when retention period expires. The retention period starts for a table when a new version of the table is created in the database.
ALTER TRIGGER	Enables or disables a trigger or changes its creation timestamp.
AS JSON	Composes data from table columns into a text format JSON document.
BEGIN LOGGING	Starts auditing 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.
BEGIN TRANSACTION	Defines the beginning of an explicit logical transaction in Teradata session mode.
CALL	Invokes an SQL procedure or external stored procedure.
CHECKPOINT	Places a flag in a journal table that can be used to coordinate transaction recovery.
COLLECT DEMOGRAPHICS	Collects table demographic estimates and writes the data to the DataDemographics table of a user-defined QCD database for subsequent analysis.
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.
COLLECT STATISTICS (QCD Form)	Collects demographic data for one or more columns and indexes of a table, computes a statistical profile of the collected data, and stores the synopsis in the TableStatistics table of the specified QCD database.
COMMENT (Comment-Placing Form)	Creates a user-defined description of a user-defined database object or definition in the data dictionary.
COMMENT (Comment-Retrieving Form)	Retrieves a previously placed comment on a database object or definition.
COMMENT ON COMPUTE GROUP	Shows, adds, or replaces the descriptive comment in the dictionary definition of a compute cluster group.
COMMENT ON COMPUTE PROFILE	Shows, adds, or replaces the descriptive comment in the dictionary definition of a compute profile.
COMMENT ON DATALAKE	Shows, adds, or replaces the descriptive comment in the dictionary definition of a Datalake object.
COMMIT	Terminates the current ANSI session mode SQL transaction, commits all changes made within it, and drops the Transient Journal for the transaction.
CREATE AUTHORIZATION	Creates or replaces an authorization object.
CREATE COMPUTE GROUP	Creates a compute cluster group—a group of compute cluster profiles and a policy for assigning access and administration privileges to their compute cluster users.
CREATE CONSTRAINT	Creates the SQL row-level security constraint definition and associates it with specific UDFs to enforce that constraint.
CREATE COMPUTE PROFILE	Creates a compute profile for compute clusters in the same compute group that have the same compute map. The compute profile specifies the scaling policy for these compute clusters.
CREATE DATABASE	Creates a database in which other database objects can be created.
CREATE DATABASE (Datalake Form)	Creates database metadata in the Open Table Format catalog for a Datalake object created with the CREATE DATALAKE and REPLACE DATALAKE statement.
CREATE DATALAKE	Creates a DATALAKE object and grants the creator the following privileges on the SELECT privilege WITH GRANT OPTION option or SHOW privilege WITH GRANT OPTION object.
CREATE ERROR TABLE (OFS)	Runs foreign table queries on compute clusters where the error table is associated with the foreign table specified in the CREATE ERROR TABLE statement contains rows that came from queries against the foreign table.
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 Teradata 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 (SQL Form)	Creates an SQL UDF.
CREATE FUNCTION (Table Form)	Creates a table function definition.
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 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 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 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, copies data and statistics to the new table.
CREATE TABLE (Alias Table Form)	Creates an alias table for a Datalake table. The alias table allows you reference the Datalake table using the syntax you use for any other VantageCloud Lake, instead of using dot notation.
CREATE TABLE (Datalake Form)	Creates an Open Table Format table in a Datalake.
CREATE TABLE AS (Datalake Form)	Creates an Open Table Format table in a Datalake using columns from an existing Block File System, Object File System, or Open Table Format table, with or without its data.
CREATE TABLE and CREATE TABLE AS (Object File System Table Form)	Creates a table in native object storage (an Object File System table). CREATE TABLE AS copies the new table column definitions, and optionally its data and statistics, from an existing table. The existing table need not be an Object File System table.
CREATE TABLE (Queue Table Form)	Creates a queue table.
CREATE TRIGGER	Creates a new trigger definition.
CREATE USER	Creates a permanent database user object, in which other database objects may be created, with a required permanent space allocation and a required password plus optional attributes.
CREATE VIEW	Creates a view on a set of tables or views or both.
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	Removes one or more rows from a table.
DELETE (Datalake Form)	Removes one or more rows from a Datalake table.
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 COMPUTE GROUP	Removes an existing compute cluster group from the database.
DROP COMPUTE PROFILE	Removes an existing compute profile from the database, destroying all compute clusters associated with the compute profile.
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 DATABASE (Datalake Form)	Drops database metadata in the Open Table Format catalog for a Datalake object.
DROP DATALAKE	Removes an existing Datalake object from VantageCloud Lake.
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 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 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 STATISTICS (Optimizer Form)	Drops the statistical data that was collected for specified columns of a table 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 STATISTICS (QCD Form)	Drops sampled statistics on the specified table from the TableStatistics table in the specified QCD_name.
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 TABLE (Datalake Form)	Removes an Open Table Format table in the Datalake metadata.
DROP TRIGGER	Drops the definition for the specified trigger from its subject table.
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.
DUMP EXPLAIN	Returns the Optimizer plan information for a query to the requestor in the form of a script containing a series of INSERT requests.
ECHO	Returns a fixed character string to the requestor.
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.
END TRANSACTION	Defines the completion of an explicit Teradata session mode transaction, commits the transaction, and drops its Transient Journal. This statement is only supported on the Block File System on the primary cluster. It is not available for the Object File System.
EXECUTE (Macro Form)	Performs a macro.
EXECUTE FUNCTION	Enables an analytic function within a database.
FLUSH QUERY LOGGING	Flushes one, several, or all DBQL caches or workload management caches to disk.
GIVE	Transfers ownership of a database or user space to another user. Also transfers all databases and users owned by the transferred database or user.
GRANT	Establishes explicit privileges for one or more users, proxy users, databases, or roles.
GRANT (Monitor Form)	Grants system-wide performance monitoring privileges.
GRANT (Role Form)	Grants roles to users or other roles.
GRANT (SQL Form)	Grants one or more explicit privileges to a role, group of roles, user, or group of users or databases.
GRANT COMPUTE GROUP	Gives access to the specified compute group so that the user can run queries in the compute group.
GRANT CONNECT THROUGH	Grants the privilege to connect as a proxy permanent user or proxy application user through the specified trusted user, storing the information in DBC.ConnectRulesTbl.
GRANT CREATE COMPUTE GROUP	Gives a user or role the ability to create compute groups.
GRANT CREATE COMPUTE PROFILE	Gives a user or role the ability to create compute profiles.
GRANT DROP COMPUTE GROUP	Gives a user or role the ability to drop compute groups.
GRANT DROP COMPUTE PROFILE	Gives a user or role the ability to drop compute profiles.
GRANT LOGON	Gives specific users permission to log on to the database from one or more specific client systems, or changes the current system logon defaults.
GRANT MAP	Grant existing contiguous or sparse maps to users and roles.
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. See SHOW object.
HELP DATABASE	Displays the attributes, sorted by object name, for all tables, views, join indexes, SQL procedures, user-defined functions, and macros contained by a specified database.
HELP DATALAKE	Displays the names of the databases in a Datalake object.
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 INDEX	Displays the attributes for the primary and secondary indexes defined for a base data table or join index.
HELP JOIN INDEX	Displays the attributes of the columns defined by a specified join index.
HELP MACRO	Displays the attributes for the specified macro.
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 only the creation time attributes for the specified procedure.
HELP SESSION	Displays attribute information for the user of the current session or only 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 only 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 TABLE	Displays the attributes for a specified base data table.
HELP TRIGGER	Displays the attributes for a specified trigger.
HELP USER	Displays the attributes, sorted by object name, for all tables, views, join 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.
INITIATE INDEX ANALYSIS	Analyzes a query workload and generates a set of recommended secondary and single-table join indexes for optimizing its processing.
INITIATE PARTITION ANALYSIS	Analyzes a given workload to make recommendations for partitioning expressions.
INSERT/INSERT ... SELECT	Adds new rows to a table by directly specifying the row data to be inserted (valued form) or by retrieving the new row data from another table (selected, or INSERT ... SELECT form).
INSERT/INSERT ... SELECT (Datalake Form)	Adds new rows to a Datalake table.
INSERT EXPLAIN	Captures the Optimizer white tree information for a query and writes the information to a user-defined query capture database, optionally as an XML document.
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.
MERGE	Merges a source row set into a primary-indexed target table based on whether any target rows satisfy a specified matching condition with the source row.
MODIFY COMPUTE GROUP	Changes the query strategy of an empty compute cluster group (a compute cluster group without compute cluster profiles).
MODIFY COMPUTE PROFILE	Changes an existing compute profile—that is, changes the scaling policy for the compute clusters in the compute profile.
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.
Null	Provides a method for including a blank line or self-contained comment within an SQL request, such as a multiple-statement request, macro, stored procedure, and so on.
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 DATALAKE	Replaces a DATALAKE object and grants the creator the following privileges on the SELECT privilege WITH GRANT OPTION option or SHOW privilege WITH GRANT OPTION object.
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 (Table Form)	Replaces a table function definition.
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 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 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.
REVOKE	REVOKE rescinds explicit privileges from one or more users, proxy users, databases, or roles. The privileges may have been granted automatically or by a previous GRANT statement.
RESTART INDEX ANALYSIS	Restarts a previously halted index analysis started by an INITIATE INDEX ANALYSIS or RESTART INDEX ANALYSIS request that specified a checkpoint.
REVOKE (Monitor Form)	Revokes system-wide performance monitoring privileges. REVOKE MONITOR takes effect immediately when the revoked users issue their next statement.
REVOKE (Role Form)	Revokes a role from users or other roles.
REVOKE (SQL Form)	Revokes one or more explicit privileges on a database, user, table, view, procedure, UDF, function mapping, or macro from a role, group of roles, user, or group of users or removes the GRANT option from explicit privileges.
REVOKE CONNECT THROUGH	Revokes an existing proxy CONNECT THROUGH privilege from a permanent user or application user.
REVOKE CREATE COMPUTE GROUP	Revokes the ability for a user or role to create compute groups.
REVOKE CREATE COMPUTE PROFILE	Revokes the ability for a user or role to create compute profiles.
REVOKE DROP COMPUTE GROUP	Revokes the ability for a user or role to drop compute groups.
REVOKE DROP COMPUTE PROFILE	Revokes the ability for a user or role to drop compute profiles.
REVOKE LOGON	Revokes permission to log on to the database from one or more specific client systems, or changes the current system logon defaults.
REVOKE MAP	Revoke existing contiguous maps from users and roles.
ROLLBACK	Terminates and rolls back the current transaction.
SELECT	Returns specific row data in the form of a result table.
SELECT AND CONSUME	Returns data from the row with the oldest insertion timestamp in the specified queue table and deletes the row from the queue table.
SELECT ... AS OF TIMESTAMP	Selects the rows of an Object File System table that existed at a specified timestamp.
SELECT ... AS OF version_number	Selects the rows of an Object File System table that existed in a specified version.
SELECT ... INTO	Selects at most one row from a table and assigns the values in that row to host variables in an embedded SQL application or to local variables or parameters in stored procedures.
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 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 supported and character codes reserved for future use.
SET SESSION COLLATION	Changes the collation sequence for the current session.
SET SESSION COMPUTE GROUP	Sets your session compute cluster group.
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 DOT NOTATION	Sets the session response for dot notation query results that return a list of values.
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 SESSION UDFSEARCHPATH	Specifies a custom UDF search path.
SET TIME ZONE	Changes the default time zone displacement for a session.
SHOW COMPUTE GROUP	Shows the statement that created the compute cluster group as text or XML.
SHOW COMPUTE PROFILE	Shows the statement that created the compute profile as text or XML.
SHOW DATALAKE	Shows the definition of an existing Datalake object with the name value pairs that the associated table operators use to connect to the Datalake.
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.
UPDATE	Modifies column values in existing rows of a table.
UPDATE (Datalake Form)	Modifies column values in existing rows of a Datalake table.
UPDATE (Upsert Form)	Updates column values in a specified row and, if the row does not exist, inserts the row into the table with a specified set of initial column values. The table must have a primary index and cannot be column partitioned.
WRITE_NOS	Writes data from Vantage to external object storage outside of VantageCloud Lake.