Is there any in-built function or proc in Snowflake which can return boolean value. if table exist or not.
like
cal IF_table_exist('table_name') or select iftableexist('table_name');
If not then I am planning to write a store proc which will solve the purpose. Any direction will be very helpful.
Thanks in advance.
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.
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';
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
$$;