search all database tables for occurrence string

Database

search all database tables for occurrence string

Hi all,

Is it possible to search all tables of a teradata database for a string (e.g. xyz), to determine which tables and columns contain this string. It is possible in Sql server but several google seraches did not reveal much for TeraData. Thanks experts.

C


Accepted Solutions
Teradata Employee

Re: search all database tables for occurrence string

Teradata is a Massively Parallel Processing database.  It is not just a bunch of files you can grep.  The only way to access data is to write SQL statements or utility scripts.

Therefore, you have to begin with the prior suggestion of reading dbc.tables and dbc.columns to identify all the tables and their columns.  Then I can imagine two options.  One would would be to write an SQL statement for each table that looks like:

   Select * from <table-name> where <column-1> like '%<the-string>%' or <column-2> like '%<the-string>%' or ...;

It wouldn't be hard to write something quick and dirty to generate these statements.

 

The other option would be to generate fast export scripts (or TPT Export scripts) to write the data from each table to a file and then grep all those files.  This would probably require a lot of disk space.

 

1 ACCEPTED SOLUTION
7 REPLIES
Junior Contributor

Re: search all database tables for occurrence string

You can easily port the logic of a SQL Server Stored Proc to Teradata (dbc.columnsV contains all column names from all tables), but why would you want to do this? Your DBA probably doesn't like that, most systems got lots of tables, some of them at least in the double-digit TB size and you're querying every character-column in every table?

 

Both SQL Server and Teradata are Relational DBMSes, not a free-text-DBs.

Re: search all database tables for occurrence string

You can use this system table DBC.tables or DBC.columns to find all tables of a database or all columns of a database

Exemple:

select TableName

from DBC.tables

where DataBaseName='XXXX'

Re: search all database tables for occurrence string

Agree but sometimes you have no choice to determine which table and column contains an entry shown in a systems frontend. Especially if nobody documented the schema.

Re: search all database tables for occurrence string

I think you misunderstand. I am looking for a column value containing the string.
Teradata Employee

Re: search all database tables for occurrence string

Teradata is a Massively Parallel Processing database.  It is not just a bunch of files you can grep.  The only way to access data is to write SQL statements or utility scripts.

Therefore, you have to begin with the prior suggestion of reading dbc.tables and dbc.columns to identify all the tables and their columns.  Then I can imagine two options.  One would would be to write an SQL statement for each table that looks like:

   Select * from <table-name> where <column-1> like '%<the-string>%' or <column-2> like '%<the-string>%' or ...;

It wouldn't be hard to write something quick and dirty to generate these statements.

 

The other option would be to generate fast export scripts (or TPT Export scripts) to write the data from each table to a file and then grep all those files.  This would probably require a lot of disk space.

 

Re: search all database tables for occurrence string

As I said I understand. BTW this is the SQL server script: http://www.setzkorn.eu/?p=25
Teradata Employee

Re: search all database tables for occurrence string

I see. Yes, you could do that in Teradata Stored Procedure language too, reading dbc.tables and dbc.columns, although I was thinking shell scripts - you can restart them manually if they have to be killed.  SPL uses || instead of + for concatenation, and variable names don't have to start with "@", but sometimes they have to be preceded by ":".  I would change the EXEC'ed Select statement at the end to use OR's as in my example above, so you don't have to scan each table once for each column!  Maybe in a tiny SQL Server database it doesn't matter, but Teradata tables tend to be very large.