Object Name Resolution

A fully-qualified schema object (table, view, file format etc.) has the form:

<database_name>.<schema_name>.<object_name>

However, since this can be tedious to write, the user is allowed to omit qualifications, from left to right. This topic describes how schema object names are resolved.

In this Topic:

Resolution When Database Omitted

(''<schema_name>.<object_name>'')

The object name is augmented with the current database. The current database is set to a default value, depending on the account’s settings, when a session is initiated. Afterwards, it can be changed using the USE DATABASE command. The CREATE DATABASE command also implicitly changes the current database to the newly created one. The name of the current database is returned by the CURRENT_DATABASE function.

For example:

SELECT CURRENT_DATABASE();

--------------------+
 CURRENT_DATABASE() |
--------------------+
 TESTDB             |
--------------------+

CREATE DATABASE db1;

------------------------------------+
               status               |
------------------------------------+
 Database DB1 successfully created. |
------------------------------------+

SELECT CURRENT_DATABASE();

--------------------+
 CURRENT_DATABASE() |
--------------------+
 DB1                |
--------------------+

USE DATABASE testdb;

----------------------------------+
              status              |
----------------------------------+
 Statement executed successfully. |
----------------------------------+

SELECT current_database();

--------------------+
 CURRENT_DATABASE() |
--------------------+
 TESTDB             |
--------------------+

Unqualified Objects

Unqualified objects (single identifiers) are resolved in two different ways, depending on whether they appear in a DDL or DML statement or in a query.

DDL and DML Statements

In DDL and DML statements, unqualified objects are augmented with the current database and schema. The current schema is maintained similarly to the current database. The current schema always belong to the current database.

When a session is initiated, the current schema is initialized based on the connection’s settings. When the current database is changed, the current schema defaults to the value of an internal property (normally set to PUBLIC). The current schema can be changed (always within the current database) by using the USE SCHEMA command. It is also implicitly changed by the CREATE SCHEMA command. The name of the current schema is returned by the CURRENT_SCHEMA function.

For example:

SELECT current_schema();

------------------+
 CURRENT_SCHEMA() |
------------------+
 TESTSCHEMA       |
------------------+

CREATE DATABASE db1;

------------------------------------+
               status               |
------------------------------------+
 Database DB1 successfully created. |
------------------------------------+

SELECT current_schema();

------------------+
 CURRENT_SCHEMA() |
------------------+
 PUBLIC           |
------------------+

CREATE SCHEMA sch1;

-----------------------------------+
              status               |
-----------------------------------+
 Schema SCH1 successfully created. |
-----------------------------------+

SELECT current_schema();

------------------+
 CURRENT_SCHEMA() |
------------------+
 SCH1             |
------------------+

USE SCHEMA public;

----------------------------------+
              status              |
----------------------------------+
 Statement executed successfully. |
----------------------------------+

SELECT current_schema();

------------------+
 CURRENT_SCHEMA() |
------------------+
 PUBLIC           |
------------------+

Queries

In queries, unqualified objects are resolved through a search path. The value of the search path is a comma-separated list of identifiers. Currently, the search path consists of the following pseudo-variables and cannot be modified:

$current
Specifies the current schema (see above).
$public
Specifies the public schema of the current database. The public schema’s name is determined by an internal property, maintained by Snowflake, that is typically set to the PUBLIC (for the PUBLIC schema automatically created for each database).

To see the schemas that will be searched for unqualified objects in queries, use the CURRENT_SCHEMAS function. The return value for the function contains a series of fully-qualified schemas in the search path, separated by commas.

For example:

select current_schemas();

+-------------------+
| CURRENT_SCHEMAS() |
|-------------------|
| []                |
+-------------------+

use database mytestdb;

select current_schemas();

+---------------------+
| CURRENT_SCHEMAS()   |
|---------------------|
| ["MYTESTDB.PUBLIC"] |
+---------------------+

create schema private;

select current_schemas();

+-----------------------------------------+
| CURRENT_SCHEMAS()                       |
|-----------------------------------------|
| ["MYTESTDB.PRIVATE", "MYTESTDB.PUBLIC"] |
+-----------------------------------------+