Get query metadata without running it

Database
Teradata Employee

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!

8 REPLIES
Enthusiast

Re: Get query metadata without running it

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,

Teradata Employee

Re: Get query metadata without running it

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

Teradata Employee

Re: Get query metadata without running it

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

Teradata Employee

Re: Get query metadata without running it

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.

Teradata Employee

Re: Get query metadata without running it

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.

Teradata Employee

Re: Get query metadata without running it

Here is another alternative that doesn't require the creation of the volatile table:

help column * from (select * from dbc.dbcinfo) dt

Teradata Employee

Re: Get query metadata without running it

Brilliant!

Thanks

Teradata Employee

Re: Get query metadata without running it

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.