CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
danD
danD

Reputation: 736

Snowflake check if table exist

Is there any in-built function or procedure in Snowflake which can return boolean value to indicate whether a table exists or not?

Like

cal IF_table_exist('table_name') or select iftableexist('table_name');

If not then I am planning to write a stored procedure which will solve the purpose. Any direction will be very helpful.

Thanks in advance

Upvotes: 2

Views: 21195

Answers (4)

tjoelbone
tjoelbone

Reputation: 1

If you are checking whether or not a table exists for DDL, it's easiest to use the following:

CREATE OR REPLACE Table_Name (
col1
col2
.
.
coln
);

Upvotes: 0

Phillip Perin
Phillip Perin

Reputation: 61

The function EXISTS is can be used in Snowflake to check if a table exists

CREATE TABLE EXAMPLE_TABLE (
  COL1 VARCHAR
);


EXECUTE IMMEDIATE
$$
BEGIN

    IF (EXISTS(SELECT * FROM MY_DATABASE.INFORMATION_SCHEMA
               WHERE TABLE_NAME = 'EXAMPLE_TABLE'
              )
        THEN RETURN 'EXISTS';
        
    ELSE 
        RETURN 'NOT EXISTS'
        
    END IF;
END
$$;

Upvotes: 1

Dave Welden
Dave Welden

Reputation: 1951

Minimal implementation for the function (you could add more error handling, etc.)

CREATE OR REPLACE FUNCTION TBL_EXIST(SCH VARCHAR, TBL VARCHAR)
  RETURNS BOOLEAN
  LANGUAGE SQL
AS
  'select to_boolean(count(1)) from information_schema.tables where table_schema = sch and table_name = tbl';

Upvotes: 6

Gokhan Atil
Gokhan Atil

Reputation: 10144

No, there is not a built-in function in Snowflake to check if the table exists and return a boolean value. It's possible to check the table using SHOW TABLES command.

https://docs.snowflake.com/en/sql-reference/sql/show-tables.html

So you may parse the output of the command to return a boolean value.

Upvotes: 1

Related Questions