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
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';
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.
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
$$;
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
);
