Table

This page provides a comprehensive overview of table operations in Databend, organized by functionality for easy reference.

Table Creation

Command	Description
CREATE TABLE	Creates a new table with specified columns and options
CREATE TABLE ... LIKE	Creates a table with the same column definitions as an existing one
CREATE TABLE ... AS	Creates a table and inserts data based on the results of a SELECT query
CREATE TRANSIENT TABLE	Creates a table without Time Travel support
CREATE EXTERNAL TABLE	Creates a table with data stored in a specified external location
ATTACH TABLE	Creates a table by associating it with an existing table

Table Modification

Command	Description
ALTER TABLE COLUMN	Modifies the structure of a table by making changes to its columns
ALTER TABLE CONNECTION	Updates the connection settings for an external table
ALTER TABLE OPTION	Modifies the Fuse engine options of a table
ALTER TABLE COMMENT	Updates the comment for a table
RENAME TABLE	Changes the name of a table

Table Information

Command	Description
DESCRIBE TABLE / SHOW FIELDS	Shows information about the columns in a given table
SHOW FULL COLUMNS	Retrieves comprehensive details about the columns in a given table
SHOW CREATE TABLE	Shows the CREATE TABLE statement that creates the named table
SHOW TABLES	Lists the tables in the current or a specified database
SHOW TABLE STATUS	Shows the status of the tables in a database
SHOW DROP TABLES	Lists the dropped tables in the current or a specified database

Table Deletion & Recovery

Command	Description	Recovery Option
TRUNCATE TABLE	Removes all data from a table while preserving the table's schema	FLASHBACK TABLE
DROP TABLE	Deletes a table	UNDROP TABLE
VACUUM TABLE	Permanently removes historical data files of a table (Enterprise Edition)	Not recoverable
VACUUM DROP TABLE	Permanently removes data files of dropped tables (Enterprise Edition)	Not recoverable

Table Optimization

Command	Description
OPTIMIZE TABLE	Compacts or purges historical data to save storage space and enhance query performance
SET CLUSTER KEY	Configures a cluster key to enhance query performance for large tables

note

Table optimization is an advanced operation. Please carefully read the documentation before proceeding to avoid potential data loss.