How do I check if a temporary table exists in SQL Anywhere?

Question

I want to write a SQL IF statement that checks whether or not a local temporary table exists, but those kinds of tables are not recorded in the SQL Anywhere system catalog.

Answer 1

just try to drop it anyways and ignore the error...

BEGIN
DROP TABLE table;
EXCEPTION WHEN OTHERS THEN

END;

Answer 2

If you're asking the question, "How do I drop a local temporary table without raising an error if it doesn't exist?" then the answer's simple: just DROP it and ignore any error:

BEGIN
   DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

If you really need to know the answer to the question "Does table t exist?" you can query the table and analyze the resulting SQLSTATE. The following function makes use of several features:

• ON EXCEPTION RESUME ignores any exception raised by the SELECT and passes control to the IF statement.

• EXECUTE IMMEDIATE lets you write a query where the table name is in a string variable.

• TOP 1 makes sure that only one row is selected even if the table contains a million rows.

• ORDER BY 1 lets you meet the requirement that TOP can only be used when the result set is ordered.

• SELECT 1 relieves you of the burden of specifying a column name.

• INTO @dummy means the SELECT (and consequently the EXECUTE IMMEDIATE) doesn't return a result set.

If the SELECT works, it's either going to set SQLSTATE to '00000' for success or '02000' for row not found. Any other SQLSTATE means there's some serious problem with the table... like it doesn't exist.

CREATE FUNCTION f_table_is_ok
   ( IN @table_name VARCHAR ( 128 ) )
   RETURNS INTEGER
   ON EXCEPTION RESUME
BEGIN
   DECLARE @dummy INTEGER;
   EXECUTE IMMEDIATE STRING (
      'SELECT TOP 1 1 INTO @dummy FROM ',
      @table_name,
      ' ORDER BY 1' );
   IF SQLSTATE IN ( '00000', '02000' ) THEN
      RETURN 1
   ELSE
      RETURN 0
   END IF;
END;

Here's some test code:

BEGIN
DECLARE LOCAL TEMPORARY TABLE tt ( c INTEGER );
DECLARE LOCAL TEMPORARY TABLE "t t" ( c INTEGER );
SELECT f_table_is_ok ( 'asdf' );
SELECT f_table_is_ok ( 'tt' );
SELECT f_table_is_ok ( '"t t"' );
SELECT f_table_is_ok ( '"SYS"."SYSTABLE"' );
END;

Answer 3

Note that you can do this in 11.0.1 and higher:

DROP TABLE IF EXISTS t;

Answer 4

Breck, THANKS FOR SHARING YOUR CODE . It was helpfull to quickly solve a problem on a sunny saturday at the office. Oscar As-Py