Archives of the TeradataForum

Message Posted: Mon, 24 Feb 2003 @ 12:24:02 GMT

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

Adriano,

Create the macro shown below...

REPLACE MACRO get_ddl
(indatabase VARCHAR(30), types CHAR(10))
AS
(ECHO '.TITLEDASHES OFF;';
 ECHO '.OMIT ON 1;';

 SELECT

 CASE
 WHEN TableKind = 'T' THEN 1
 WHEN TableKind = 'V' THEN 2
 WHEN TableKind = 'M' THEN 3
 WHEN TableKind = 'G' THEN 4
 WHEN TableKind = 'I' THEN 5
 ELSE 6
 END,

 CASE
 WHEN TableKind = 'T' AND POSITION('T' IN :types)>0
  THEN 'SHOW TABLE '||TRIM(DataBaseName)||'.'||TRIM(TableName)||';'

 WHEN TableKind = 'V' AND POSITION('V' IN :types)>0
  THEN 'SHOW VIEW '||TRIM(DataBaseName)||'.'||TRIM(TableName)||';'

 WHEN TableKind = 'M' AND POSITION('M' IN :types)>0
  THEN 'SHOW MACRO '||TRIM(DataBaseName)||'.'||TRIM(TableName)||';'

 WHEN TableKind = 'G' AND POSITION('G' IN :types)>0
  THEN 'SHOW TRIGGER '||TRIM(DataBaseName)||'.'||TRIM(TableName)||';'

 WHEN TableKind = 'I' AND POSITION('I' IN :types)>0
  THEN 'SHOW JOIN INDEX '||TRIM(DataBaseName)||'.'||TRIM(TableName)||';'

 END (CHAR (85), TITLE '')

 FROM DBC.Tables

 WHERE DataBaseName = :indatabase

 ORDER BY 1;);

Once you create this macro, execute this with the database of your choice (ex. dbc) to get all the "SHOW" commands into a file. Next execute this script containing the "SHOW"s to get all the DDL.

For instance,

.export file = all_show.bteq
exec get_ddl('dbc','tvm');

.export file = dbc_tvm_ddl.bteq
.run file = all_show.bteq

Finally you can use "dbc_tvm_ddl.bteq" to recreate the database on a different system.

Sanjay

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