Archives of the TeradataForum

Message Posted: Mon, 20 Feb 2005 @ 23:19:25 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

If you have access to a Unix system (or a Unix environment in windows - eg. Cygwin) you can easily process the list to get the tables you need.

Use bteq to export the output of the show select(s) to a text file. Then grep to isolate the lines containing the word "table" as in "create table".

Assuming the output of the show select * from... is in a file called sql.txt then the following command will extract all lines with the word table:

     grep -i table sql.txt

This will provide you with a nice list of create table statements (and any other line that contains the word table (eg. comments).

To eliminate them you could also try:

     grep -i "create set table" sql.txt

or

     grep -i "create.*table" sql.txt

the first will scan for lines containing the phrase "create set table" which is great if all your tables are "set".

The second will look for lines containing the word "create" followed by any number of characters then the word "table". It will match "Create set table", "create mutliset table" and even /* Now it is time to create our temporary table */ - i.e. comments that just happen to match this simple pattern.

With more sophistication, you can get it to match just the real create tables, but the above examples should get you close enough.

If you want to get really excited you can actually produce a report broken down by view name (eg. View X references tables A, B and C). This can be fairly easily produced with perl.

On a project I was working on, we were experiencing a deadlock problem with some macros. We needed to scan every macro looking for UPDATE, INSERT and DELETE statements. So we produced a perl script which scanned every macro looking for UPDATE, INSERT and DELETE statements. Then it modified each macro to contain the appropriate "locking XXX for write".

Hope this helps

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>