If you're prepared to dive into some External Stored Procedures (XSP), you can issue a SHOW dynamically.
Check out the link below for details and some sample code on how to do it using some relatively simple Java code.
P.S. Note the disclaimer on the linked page about supportability. i.e. it has worked in tests may not be formally supported by Teradata.
I won't claim to be Fred, but I think you may be after something like the following macro (you can do this in a stored procedure, but I had this code handy, so if it helps):
replace macro <MY_DB>.GetShowDBObjects ( i_DatabaseNames varchar(1000) ,i_TableNames varchar(1000) ) as ( select case when tbl.tablekind = 'T' then 'show table ' when tbl.tablekind = 'V' then 'show view ' end || trim(databasename) || '.' || trim(tablename) || ' ;' (title '') from DBC.TablesV tbl inner join ( select trim(o_token) as fq_pattern from table ( strtok_split_to_table ( 1 ,trim(:i_databaseNames) ,',') returns ( o_Key integer ,o_tokNum integer ,o_token varchar(257) character set unicode) ) as tf_Token ) as db_list on tbl.databasename like db_list.fq_pattern escape '\' inner join ( select trim(o_token) as fq_pattern from table ( strtok_split_to_table ( 1 ,trim(:i_tablenames) ,',') returns ( o_Key integer ,o_tokNum integer ,o_token varchar(257) character set unicode) ) as tf_Token ) as tbl_list on tbl.tablename like tbl_list.fq_pattern escape '\' where tbl.tablekind in ('T','O','V') order by case when Tablekind = 'T' then 1 else 2 end ; );
This will mean you can call the macro as follows:
e.g.1. To get a list of show statement for all ('%') tables and views that are in Databases with names like 'DBC' or 'E%':
e.g.2.To get a list of show statement for tables (including NOPI tables) and views that are in Databases with names like 'DBC' or 'E_%' and that have names that start with 'D' or 'I':
Hope that helps!
(still not Fred )
If you want to return the results of a dynamic SQL statement, you must
CREATE PROCEDURE ...
... DYNAMIC RESULT SETS 1 ...
DECLARE SqlString VARCHAR(1024);
DECLARE csr CURSOR WITH RETURN FOR pstmt;
/* Create the SQL statement string */
PREPARE pstmt FROM SqlString;
If you actually are building answer set rows via procedure logic rather than wanting to directly return results from a SQL statement, use a Global Temporary table. Have the procedure insert the rows into (a materialized instance of) the GTT and then use a CURSOR WITH RETURN to pass the contents of the GTT back to the caller.