All Forums General
danielA 8 posts Joined 06/13
16 Sep 2014
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

Raja_KT 1246 posts Joined 07/09
16 Sep 2014

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'

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

tomnolan 594 posts Joined 01/08
16 Sep 2014

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)

You must sign in to leave a comment.