How to get the name of a table from an Insert, Update or Delete SQL without parsing

Database

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!

2 REPLIES
Teradata Employee

Re: How to get the name of a table from an Insert, Update or Delete SQL without parsing

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.

Re: How to get the name of a table from an Insert, Update or Delete SQL without parsing

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".