Question Re: How to retrieve temporary table structure? in Technology Q&A

How to retrieve temporary table structure?

Valdas — Mon, 14 Mar 2022 08:14:20 GMT

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?

Re: How to retrieve temporary table structure?

VolkerBarth — Mon, 14 Mar 2022 08:56:27 GMT

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.

Re: How to retrieve temporary table structure?

Valdas — Mon, 14 Mar 2022 09:20:23 GMT

I found a workaround:

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

Re: How to retrieve temporary table structure?

VolkerBarth — Mon, 14 Mar 2022 09:24:07 GMT

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