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.
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
| Option | Description |
|---|---|
| CASCADE | Drops database, including tables with primary and unique keys that are referenced by foreign keys |
| RESTRICT | Returns warning about foreign key references and does not drop database |
Example 13. Drop a database
DROP DATABASE DEVSNOW2;
DEVSNOW2 successfully dropped.Example 14. Undrop a database
UNDROP DATABASE DEVSNOW2;
Database DEVSNOW2 successfully restored.
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.