Are a tables columns accessible as a CSV list in DBC?

General
Enthusiast

Are a tables columns accessible as a CSV list in DBC?

Hi;

I know that some DBC tables contain a partial list of columns for each table such as:

SELECT TABLENAME , columnname  
FROM dbc.multicolumnstatsV

And that all columns are contained in:

SELECT databasename, TABLENAME , columnname 
FROM dbc.columns

But does the full list exist anywhere in DBC ? 

I want  <DATABASE_NAME> | <TABLE_NAME> | <COLUMN_NAME_CSV_LIST> as I want to generate the SELECT statement or qualified INSERTS for each table. Im not very good with procedures/cursors so will struggle to build a script to do it (Im a SQL novice)

I hope to end up with a simple statement like 

SELECT 'SELECT ' || <COLUMN_NAME_CSV_LIST> ||
'FROM ' || <DATABASE_NAME> || '.' || <TABLE_NAME>
FROM DBC.??????
WHERE <DATABASE_NAME> LIKE 'My_DB%'

Thanks in advance! Any suggestions welcome about this or a different approch

2 REPLIES
Enthusiast

Re: Are a tables columns accessible as a CSV list in DBC?

Not that I know. It seems you want all columns from a database and table something like this:

select tdstats.udfconcat(trim(columnname)) from dbc.columns where databasename='yourdb' and tablename='t1'

Teradata Employee

Re: Are a tables columns accessible as a CSV list in DBC?

If you want to select all columns from a table, you don't really need the column names.

You can simply do: SELECT * FROM MyDatabase.MyTable

The same is true for INSERT statements. If you are inserting a value for every column in the table, you can omit the column list:

INSERT INTO MyTable VALUES (value1, value2, ..., valueN)