All Forums Database
padhia 35 posts Joined 06/10
27 Jan 2014
Get query metadata without running it

If I have a SQL (select type) query, how would I find out properties of the resultset columns without actually running it? For example, if I were to crerate a view "V" using my query SQL and then I would be able to use HELP COLUMN V.* to get properties of all columns that are result of my query. Another alternative I could think of was, to use CREATE VOLATILE TABLE AS <query> WITH NO DATA and then again use HELP COLUMN to get column properties.
How can I do this without creating the view or a volatile table? DB2 for example, has DESCRIBE OUTPUT statement which would return metadata about query resultset without actually having to run the query. 
Are there any better alternatives?
 
Thanks!

Raja_KT 1246 posts Joined 07/09
27 Jan 2014

I am not able to understand clearly. However, let me try to answer. Is this what you are talking?
show select * from DB1.raja_test----raja_test is a table.
Cheers,
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

padhia 35 posts Joined 06/10
28 Jan 2014

Hello Raja,
 
Thanks for your response and sorry if my question was a bit confusing. What I wanted was, "given an arbitrary SQL statement, how would I find out number and types of columns that will be returned when executed in advance?". This requirement is primarily due to a script I am building that will use ODBC interface and needs to know the nature of resultset without having to execute the query. (Very similar to how SQL Assistant knows how to correctly display any resultset, except it knows this after the execution of SQL statement)
 
show <query> displays the DDLs of the involved objects, which isn't what I am looking for.
 
Regards

Fred 1096 posts Joined 08/04
28 Jan 2014

Since this is a program using embedded SQL, have you looked at PREPARE (ODBC SQLPrepare method)?

padhia 35 posts Joined 06/10
28 Jan 2014

Fred, that would have been an excellent suggestion, but unfortunately, I am using Python as scripting language. Python's DB API implementation (pyodbc) doesn't expose ODBC's SQLPrepare method. I know it's Python's limitation, but I was hoping to circumvent it by some facility within Teradata (similar to HELP COLUMN).
 
Thanks for your response though.

tomnolan 594 posts Joined 01/08
28 Jan 2014

I verified that the CREATE VOLATILE TABLE approach works:
create volatile table foo as (select * from dbc.dbcinfo) with no data on commit delete rows
help column foo.*
 
 
This approach is probably more useful than the CREATE VIEW approach, because (1) all users can create volatile tables, whereas not all users may have permission to create views, and (2) the volatile table will be cleaned up automatically when the session is logged off.
 

tomnolan 594 posts Joined 01/08
28 Jan 2014

Here is another alternative that doesn't require the creation of the volatile table:
 
help column * from (select * from dbc.dbcinfo) dt
 

padhia 35 posts Joined 06/10
28 Jan 2014

Brilliant!
 
Thanks

padhia 35 posts Joined 06/10
28 Jan 2014

I just realized there is a minor flaw in all of these solutions. They won't work if the query has an un-aliased expression in SELECT caluse. For example following doesn't work.
 
help column * from (select count(*) from dbc.dbcinfo) dt 
 
Apart from such edge cases, I think this is the cleanest solution.

You must sign in to leave a comment.