I know that some DBC tables contain a partial list of columns for each table such as:
SELECT TABLENAME , columnname
And that all columns are contained in:
SELECT databasename, TABLENAME , columnname
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>
WHERE <DATABASE_NAME> LIKE 'My_DB%'
Thanks in advance! Any suggestions welcome about this or a different approch
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'
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)