All Forums Database
mszumowski 2 posts Joined 09/13
16 Sep 2013
How to get the name of a table from an Insert, Update or Delete SQL without parsing

Hello TD experts,
Is there a way to obtain the table used in a JDBC Insert, Update or Delete SQL without parsing out the table name from the actual SQL itself?
The best approach I have found (very undesirable) is to prepend EXPLAIN to the sql and parse out the table name that way. Is there a better way to do this in teradata?
Is it possible to execute a SQL and determine which table the SQL actually affected?
Thanks ahead of time!

tomnolan 594 posts Joined 01/08
17 Sep 2013

Table name is available for SELECT query results from the ResultSetMetaData.getTableName method.

But there is no easy way (and no JDBC API method) to obtain a table name for an INSERT, UPDATE, or DELETE.

Prepending EXPLAIN or SHOW QUALIFIED to the SQL request may be the best way to get the information.

mszumowski 2 posts Joined 09/13
17 Sep 2013

Thanks ahead of time for your helpful response
1. The output of EXPLAIN appears to be a bit too much to attempt to parse.
2. SHOW QUALIFIED does look managable.
I have a question:
Lets take for example we have an insert-select. When I prepend SHOW QUALIFIED to the SQL and execute, in the result set I see the following:
1. I first see the SQL
2. Second I see the DDL of the select table
3. and last I see the DDL of the insert table.
My Question: can someone speak to the rules on how that order of the tables DLL is generated?  I'm really looking to nail down a programmatic way to say "this is the table that will be modified executing this SQL".

You must sign in to leave a comment.