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

Database
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

Hello,

 

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.

 

Thanks
Abdul