Does anyone know how I can save the output of the “HELP VOLATILE TABLE” statement as a table that I can use in a query later on.
My goal is to save a list of all Volatile Tables that are currently present.
I tried to use the “HELP VOLATILE TABLE” in a CTE, but it doesn’t do the trick. It refuses to run. Any help is useful.
Update: It seems HELP/SHOW statements can only return data to the client. They can’t be used in a query.
It seems it is possible to write an external stored procedure in f.e. Java that FETCHES this data and INSERTS it into a Global Temporary Table.
My question is whether someone knows how to write said external stored procedure in JAVA, and knows how to import it and use it?
For those using SAS, this is easy to do.
PROC SQL NOPRINT;
CONNECT TO TERADATA ( &connect_string. );
SELECT *
FROM CONNECTION TO TERADATA
(HELP VOLATILE TABLE);
DISCONNECT FROM TERADATA;
QUIT;
For those using Python, the same can be done easily too.
import os
import teradatasql
import pandas as pd
with teradatasql.connect('{"host":"your_host_name"}', user="", password = "") as connect:
df = pd.read_sql("HELP VOLATILE TABLE", connect)