How to retrieve temporary table structure?

[cigaras]

Title kind of says it all, here is a sample query:

BEGIN
    DECLARE LOCAL TEMPORARY TABLE tmp(id INTEGER, val VARCHAR(20));
    SELECT * FROM sys.syscolumns WHERE tname = 'tmp';
END;

Any ideas?

[cigaras · Accepted solution]

I found a workaround:

BEGIN
    DECLARE LOCAL TEMPORARY TABLE tmp(id INTEGER, val VARCHAR(20));
    SELECT * FROM sa_describe_query('select * from tmp');
END;

[VolkerBarth · Accepted solution]

Nice, I just wanted to mentioned another answer - with exactly that solution.

[VolkerBarth]

AFAIK, you cannot, because by definition local temporary tables – in contrast to permanent tables and global temporary tables – are not contained in the system catalog.

Catalog queries like in your sample or builtin functions like the sa_get_table_definition() system procedure rely on the system catalog to provide information, so they won't offer information for local temporary tables.

---

FWIW, the table SYSTAB itself specifies that the column "table_type" is set to 4 in case of a "Local temporary table", so my assumption might be wrong.