Syntax Examples for SCHEMA

  1. CREATE SCHEMA

Example 1. Create a basic schema

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

CREATE SCHEMA DEVSCHEMA1;
Schema DEVSCHEMA1 successfully created.

Example 2. Create a managed access schema with a comment

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

CREATE SCHEMA DEVSCHEMA2 WITH MANAGED ACCESS COMMENT='Grants managed by Schema';
Schema DEVSCHEMA2 successfully created.

Example 3. Create schema with a specified Time Travel Setting

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

CREATE SCHEMA DEVSCHEMA3 WITH MANAGED ACCESS DATA_RETENTION_TIME_IN_DAYS=30;
Schema DEVSCHEMA3 successfully created.

Example 4. Create a schema with not FailSafe protection

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

CREATE TRANSIENT SCHEMA DEVSCHEMA4;
Schema DEVSCHEMA4 successfully created.

2. ALTER SCHEMA

Example 5. Rename a schema

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

ALTER SCHEMA DEVSCHEMA1 RENAME TO DEVSCHEMA5;
Statement executed successfully.

Example 6. Swap a schema with another

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

ALTER SCHEMA DEVSCHEMA2 SWAP WITH DEVSCHEMA3;
Statement executed successfully.

Example 7 Enable managed access for a schema

use role ACCOUNTADMIN;

ALTER SCHEMA DEVSCHEMA4 ENABLE MANAGED ACCESS;
Statement executed successfully.

Example 8 Alter Time Travel setting for schema

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

ALTER SCHEMA DEVSCHEMA3 SET DATA_RETENTION_TIME_IN_DAYS=30;
Statement executed successfully.

3. DROP SCHEMA

Example 9 Drop schema

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

DROP SCHEMA DEVSCHEMA3;
DEVSCHEMA3 successfully dropped.

4. UNDROP SCHEMA

Example 10 Undrop schema

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

UNDROP SCHEMA DEVSCHEMA3;
Schema DEVSCHEMA3 successfully restored.

5. SHOW SCHEMAS

Example 11. Which Schema does my current Role have access to

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

SHOW SCHEMAS;

Example 12. Which Schema does my current Role have access to include dropped schemas

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

SHOW SCHEMAS HISTORY;

Example 13. Show schema with a pattern match

USE ROLE SYSADMIN;
USE DATABASE DEVSNOW3;

SHOW SCHEMAS LIKE '%SCHEMA2';

6. USE SCHEMA

Example 14. Set current schema for session

-- example if database context already set
USE SCHEMA DEVSCHEMA2;
Statement executed successfully.

-- example if setting database context as well
USE SCHEMA DEVSNOW3.DEVSCHEMA3;
Statement executed successfully.

7, MISCELLANEOUS
Example 15. Change role ownership of a schema

use role SYSADMIN;

-- fully qualified schema with db prefix in this example
GRANT OWNERSHIP ON SCHEMA DEVSNOW3.DEVSCHEMA2 TO ROLE APPLICATION_SUPPORT;
Statement executed successfully.