cancel
Showing results for 
Search instead for 
Did you mean: 

How to retrieve temporary table structure?

cigaras
Participant
0 Kudos
796

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?

Accepted Solutions (1)

Accepted Solutions (1)

cigaras
Participant

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
Contributor
0 Kudos

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

Answers (1)

Answers (1)

VolkerBarth
Contributor
0 Kudos

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.