Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: ARC/RESTORE: - Copy database with No data.
 
From:   Gupta, Vipin

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023