Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.snowflake.com/en/sql-reference/sql/undrop-schema below:

Website Navigation

• Reference
• SQL command reference
• Databases, schemas, & shares

UNDROP SCHEMA | Snowflake Documentation

Restore the most recent version of a dropped schema.

See also:

CREATE SCHEMA , ALTER SCHEMA , DESCRIBE SCHEMA , DROP SCHEMA , SHOW SCHEMAS

name

Specifies the identifier for the schema to restore. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

• A schema can only be restored to the database that contained the schema at the time of its deletion. For example, if you create and drop schema s1 in database db1, then change the current database to db2 and attempt to restore schema s1 by ID (or fully-qualified name, db1.s1), schema s1 is restored in database db1 rather than in the current database, db2.

• If a schema with the same name already exists, an error is returned.

• UNDROP relies on the Snowflake Time Travel feature. An object can be restored only if the object was deleted within the Data retention period. The default value is 24 hours.

• Hybrid tables that belong to the specified schema are not undropped.

• If you have multiple dropped schemas with the same name, you can use the IDENTIFIER keyword with the system-generated identifier (from the SCHEMATA view) to specify which schema to restore. The name of the restored schema remains the same. See Examples.

  Note

  You can only use the system-generated identifier with the IDENTIFIER() keyword when executing the UNDROP command for notebooks, tables, schemas, and databases.

Restore the most recent version of a dropped schema (this example builds on the examples provided for DROP SCHEMA):

+----------------------------------------+
| status                                 |
|----------------------------------------|
| Schema MYSCHEMA successfully restored. |
+----------------------------------------+

+---------------------------------+--------------------+------------+------------+---------------+--------+-----------------------------------------------------------+---------+----------------+------------+
| created_on                      | name               | is_default | is_current | database_name | owner  | comment                                                   | options | retention_time | dropped_on |
|---------------------------------+--------------------+------------+------------+---------------+--------+-----------------------------------------------------------+---------+----------------+------------|
| Fri, 13 May 2016 17:26:07 -0700 | INFORMATION_SCHEMA | N          | N          | MYTESTDB      |        | Views describing the contents of schemas in this database |         |              1 | [NULL]     |
| Tue, 17 Mar 2015 17:18:42 -0700 | MYSCHEMA           | N          | N          | MYTESTDB      | PUBLIC |                                                           |         |              1 | [NULL]     |
| Tue, 17 Mar 2015 16:57:04 -0700 | PUBLIC             | N          | Y          | MYTESTDB      | PUBLIC |                                                           |         |              1 | [NULL]     |
+---------------------------------+--------------------+------------+------------+---------------+--------+-----------------------------------------------------------+---------+----------------+------------+

Restore a dropped schema by ID using IDENTIFIER(). You can find the schema ID of the specific schema to undrop using the schema_id column in the SCHEMATA view. For example, if you have multiple dropped schemas named s1, and you want to restore the second-to-last dropped schema s1, follow these steps:

1. Find the schema ID of the dropped schema in the Account Usage SCHEMATA view:

   SELECT schema_id,
     schema_name,
     catalog_name,
     created,
     deleted,
     comment
   FROM SNOWFLAKE.ACCOUNT_USAGE.SCHEMATA
   WHERE schema_name = 'S1'
   AND catalog_name = 'DB1'
   AND deleted IS NOT NULL
   ORDER BY deleted;
   

   Copy

   +-----------+-------------+---------------+-------------------------------+-------------------------------+---------+
   | SCHEMA_ID | SCHEMA_NAME | CATALOG_NAME  | CREATED                       | DELETED                       | COMMENT |
   |-----------+-------------+---------------+-------------------------------+-------------------------------+---------|
   |       797 | S1          | DB1           | 2024-07-01 17:53:01.955 -0700 | 2024-07-01 17:53:11.889 -0700 | NULL    |
   |       798 | S1          | DB1           | 2024-07-01 17:53:11.889 -0700 | 2024-07-01 17:53:16.327 -0700 | NULL    |
   |       799 | S1          | DB1           | 2024-07-01 17:53:16.327 -0700 | 2024-07-01 17:53:25.066 -0700 | NULL    |
   +-----------+-------------+---------------+-------------------------------+-------------------------------+---------+
   

2. Undrop schema s1 using schema ID. To restore the second-to-last deleted schema, use schema ID 798 from the output of the previous statement. After you execute the following statement, the schema is restored with its original name, s1:

   UNDROP SCHEMA IDENTIFIER(798);
   

   Copy

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4