Archives of the TeradataForum

Message Posted: Wed, 13 Oct 2010 @ 11:50:02 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Ravi,

You can use following SQL directly to generate create table as commands for all the tables listed a database:

     select 'create table '||'targetdb.'||tvmname||' as
     sourcedb.'||tvmname||' with no data;' as "ct command" from dbc.tvm where
     databaseid in (sel databaseid from dbc.dbase where
     databasename = 'sourcedb');

Simply redirect output to a file and use that file to run new script to create tables in target database.

Here is sample output where I want to copy all the tables from database sourcedb to database targetdb:

     help database sourcedb;

      *** Help information returned. 3 rows.
      *** Total elapsed time was 1 second.

     Table/View/Macro name          Kind Comment
     ------------------------------ ---- ------------
     t1                             T    ?
     t2                             T    ?
     t3                             T    ?

     select 'create table '||'targetdb.'||tvmname||' as
     sourcedb.'||tvmname||' with no data;' as "ct command" from dbc.tvm where

     databaseid in (sel databaseid from dbc.dbase where databasename =
     'sourcedb');

      *** Query completed. 3 rows found. One column returned.
      *** Total elapsed time was 1 second.

     ct command
     ---------------------------------------------------------------
     create table targetdb.t2 as sourcedb.t2 with no data;
     create table targetdb.t1 as sourcedb.t1 with no data;
     create table targetdb.t3 as sourcedb.t3 with no data;

Hope this helps.

Thanks,

Vipin Gupta
TGSC DBS

	<Prev	Next>		<<First	<Prev	Next>	Last>>