Need to get a sample value from all the columns in a DB - Need help

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

Need to get a sample value from all the columns in a DB - Need help



I want to get the list of Tables, columns, and a sample value for each columns (non NULL & non Blank if possible)
for a given data base. (There are 200000 columns in Database ABC)


I wrote the script like below.


Create Table Table_list
tablename varchar(30),
columnname varchar(30),
column_value varchar(4000)
Primary Index (columnname);


--Bteq export into Table_list.txt


SELECT 'insert into Table_list SELECT '''||TABLENAME||''','''||COLUMNNAME||''',MAX('||COLUMNNAME||') FROM '||DATABASENAME||'.'||TABLENAME|| ' ;'
from dbc.columns
where databasename = 'ABC';

It exports the insert queries like below.


insert into Table_list SELECT 'table1','column1',max(column1) from ABC.table1;


There will be 200000 queries exported to the file Table_list.txt.


I was trying to run all the insert queries from Table_list.txt in a Bteq.


Issues I am facing :
1. But this is running for more than 10 hours. as there are 200000 insert queries in a single bteq.
- Is there a way to reduce script running time?
2. Few insert query gave spool space issue, as those columns are not a index columns and there is no stats as well.
- Instead of MAX(COLUMN1) approach, is there any other way, which will not give spool issue ?
I tried using "SELECT TOP 1 COLUMN1 from ABC.TABLE1 WHERE COLUMN1 IS NOT NULL AND COLUMN1 <> ' '; (It is even worse)

And, I need to run this script for multiple Databases.