Syntax Examples for DATABASE

DATABASE

  1. CREATE DATABASE

Examples 1. Basic create database command:

USE ROLE SYSADMIN;

CREATE DATABASE DEVSNOW1;
Database DEVSNOW1 successfully created.

Example 2. Create database with a comment

USE ROLE SYSADMIN;

CREATE DATABASE DEVSNOW2 COMMENT="Development Environment 2";
Database DEVSNOW2 successfully created.

Example 3. Create database, replacing existing database with the same name

USE ROLE SYSADMIN;

-- First lets see what error we get if we try to create db that already exists
SQL compilation error: Object 'DEVSNOW1' already exists.


CREATE OR REPLACE DATABASE DEVSNOW1;
Database DEVSNOW1 successfully created.

Example 4. Create database only if it does not exist already

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS DEVSNOW1;
DEVSNOW1 already exists, statement succeeded.
-- notice db already exists message

CREATE DATABASE IF NOT EXISTS DEVSNOW3;
Database DEVSNOW3 successfully created.

Example 5. Create a Transient database i.e. FAILSAFE disabled

USE ROLE SYSADMIN;

CREATE TRANSIENT DATABASE DEVSNOW4 COMMENT="Development Environment NO FAILSAFE";
Database DEVSNOW4 successfully created.

Example 6. Create database Clone

USE ROLE SYSADMIN;

CREATE DATABASE DEVSNOW3CLONE1 CLONE DEVSNOW3;
Database DEVSNOW3CLONE1 successfully created.


-- Note we can not clone a Transient database
CREATE DATABASE DEVSNOW4CLONE1 CLONE DEVSNOW4;
SQL compilation error: Transient object cannot be cloned to a permanent object.

Example 7. Create database clone from a point in the past

USE ROLE SYSADMIN;

-- Timestamp AT example
CREATE DATABASE DEVSNOW3CLONE2 CLONE DEVSNOW3 AT (timestamp => to_timestamp_tz('11/11/2021 14:10:00', 'mm/dd/yyyy hh24:mi:ss'));  
Database DEVSNOW3CLONE2 successfully created.

-- Timestamp BEFORE example
CREATE DATABASE DEVSNOW3CLONE3 CLONE DEVSNOW3 AT (timestamp => to_timestamp_tz('11/11/2021 14:08:00', 'mm/dd/yyyy hh24:mi:ss')); 
Database DEVSNOW3CLONE3 successfully created.

-- Timesstamp OFFSET of 60 seconds example
CREATE DATABASE DEVSNOW3CLONE4 CLONE DEVSNOW3 AT (offset => -60); 
Database DEVSNOW3CLONE4 successfully created.

-- Statement ID example
CREATE DATABASE DEVSNOW3CLONE5 CLONE DEVSNOW3 AT (STATEMENT=>'01a0381a-0000-0ccf-0000-368900010516');
Database DEVSNOW3CLONE5 successfully created.

Example 8. Create database with Time Travel setting specified

USE ROLE SYSADMIN;

CREATE DATABASE DEVSNOW5 DATA_RETENTION_TIME_IN_DAYS=30;
Database DEVSNOW5 successfully created.

2. ALTER DATABASE

Example 9. Rename a database

ALTER DATABASE DEVSNOW1 RENAME TO DEVSNOW10;
Statement executed successfully.

Example 10. Swap 2 databases

ALTER DATABASE DEVSNOW10 SWAP WITH DEVSNOW2;
Statement executed successfully.

Example 11. Change the Time Travel Property

ALTER DATABASE DEVSNOW2 SET DATA_RETENTION_TIME_IN_DAYS=90;
Statement executed successfully.

Example 12. Unset the Time Travel Property back to its default

ALTER DATABASE DEVSNOW4 UNSET DATA_RETENTION_TIME_IN_DAYS;
Statement executed successfully.

3. DROP DATBASE

OptionDescription
CASCADEDrops database, including tables with primary and unique keys that are referenced by foreign keys
RESTRICTReturns warning about foreign key references and does not drop database

Example 13. Drop a database

DROP DATABASE DEVSNOW2;
DEVSNOW2 successfully dropped.

4. UNDROP DATABASE

Example 14. Undrop a database

UNDROP DATABASE DEVSNOW2;
Database DEVSNOW2 successfully restored.

5. SHOW DATABASES

Example 15. Which databases does my current Role have access to in my account

show databases;
 
-- pattern match on database name
show databases LIKE '%DEV%1%';

Example 16. Include information on dropped databases

SHOW DATABASES HISTORY STARTS WITH 'DEV';

6. USE DATABASE

Example 17. Set active database

USE DATABASE DEVSNOW5;
Statement executed successfully.

7. Context Functions

Example 18. Which database am I connected to using CURRENT_DATABASE()

select CURRENT_DATABASE();

+--------------------+
| CURRENT_DATABASE() |
|--------------------|
| DEVSNOW5           |
+--------------------+

7, MISCELLANEOUS

Example 19. Change role ownership of a database

use role ACCOUNTADMIN;

GRANT OWNERSHIP ON DATABASE DEVSNOW3 TO ROLE SYSADMIN;
Statement executed successfully.