Categories:
Database, Schema, & Share DDL

ALTER DATABASE

Modifies the properties for an existing database, including changing the name of the database and changing the Time Travel data retention period (if you are using Snowflake Enterprise Edition or higher).

See also:
CREATE DATABASE , SHOW DATABASES

Syntax

ALTER DATABASE [ IF EXISTS ] <name> RENAME TO <new_db_name>

ALTER DATABASE [ IF EXISTS ] <name> SWAP WITH <target_db_name>

ALTER DATABASE [ IF EXISTS ] <name> SET [ DATA_RETENTION_TIME_IN_DAYS = <num> ] [ COMMENT = '<string_literal>' ]

ALTER DATABASE [ IF EXISTS ] <name> UNSET { DATA_RETENTION_TIME_IN_DAYS | COMMENT } [ , ... ]

Parameters

name
Specifies the identifier for the database to alter. If the identifier contains spaces, special characters, or mixed-case characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
RENAME TO new_db_name

Specifies the new identifier for the database; must be unique for your account.

For more details, see Identifier Syntax.

SWAP WITH target_db_name
Swaps all objects (schemas, tables, views, etc.) and metadata, including identifiers, between the two specified databases. Also swaps all access control privileges granted on the databases and objects they contain. SWAP WITH essentially performs a rename of both databases as a single operation.
SET ...

Specifies one (or more) properties to set for the database (separated by blank spaces, commas, or new lines):

DATA_RETENTION_TIME_IN_DAYS = num

Specifies the number of days for which Time Travel actions (CLONE and UNDROP) can be performed on the database, as well as specifying the default Time Travel retention time for all schemas created in the database.

The value you can specify depends on the Snowflake Edition you are using:

  • Standard Edition: 0 or 1
  • Enterprise Edition (or higher): 0 to 90
COMMENT = 'string_literal'
Adds a comment or overwrites an existing comment for the database.
UNSET ...

Specifies one (or more) properties and/or parameters to unset for the database, which resets them to the defaults:

  • DATA_RETENTION_TIME_IN_DAYS
  • COMMENT

You can reset multiple properties/parameters with a single ALTER statement; however, each property/parameter must be separated by a comma. When resetting a property/parameter, specify only the name; specifying a value for the property will return an error.

Usage Notes

  • To rename a database, the role used to perform the operation must have the CREATE DATABASE global privilege and OWNERSHIP privileges on the database.
  • To swap two databases, the role used to perform the operation must have OWNERSHIP privileges on both databases.

Examples

Rename database db1 to db2:

ALTER DATABASE IF EXISTS db1 RENAME TO db2;