Alter Table Vs Ins Select for Modifying Table Structure in Teradata

Conclusion

It is strongly recommend implementing Alter Table, at least start considering it. If you're concerned about availability you should bear in mind that this process will probably be scheduled out of business hours anyway.
And when you need to change the [P]PI or you just want the safeness of a copy of the old table you should definitely prefer Merge Into over good ol' Insert Select.

Please review below figure to see the Pros and Cons of using Alter/Insert Selct and Merge into options for modifying any table in Teradata.

The above conclusion is based on Dieter Blog reference link for details is as follows

http://developer.teradata.com/blog/dnoeth/2013/08/why-dont-you-use-alter-table-to-alter-a-table

Dynamic DDL Generation using BTEQ

Dynamic script can be generated using Teradata DBC tables. we would use dbc.tables to generate the DDL of all objects available in development database and later we can change that script to deploy to Test or any other environment.

You can use below BTEQ script which will actually generate the standard SHOW TABLE ; statement for all objects in development database and results will be exported to Prepare_DDL.BTEQ .

 .SET ERROROUT STDOUT
.logon 10.16.X.X/tdusre,tduserpwd
.Set Echoreq off
.Set Titledashes off
.Set Separator '|'
.Set Format off
.set width 5000
.Set Null ''
 .export file=.\Prepare_DDL.BTEQ;
       select distinct 'SHOW TABLE ' ||  TRIM(databasename) || '.' || TRIM(tablename) || ';'
from dbc.tables
where tablekind =t' and databasename in
(
'DD_DEV_ENV'

);

.export reset;
  
.Logoff;
.Quit;

You can invoke this BTEQ on command prompt as  "Bteq < Bteq_filename > FileName.logs

In next step you can open up  Prepare_DDL.BTEQ and modify it as above to execute that will give you all table DDL;s in one file. The modified file will look like as follows


.SET ERROROUT STDOUT
.logon 10.16.X.X/tdusre,tduserpwd
.Set Echoreq off
.Set Titledashes off
.Set Separator '|'
.Set Format off
.Set Null ''
.set width 5000
.export file=.\Generated_DDL.sql;

SHOW TABLE  DD_DEV_ENV.ACTION_TYPE;
SHOW TABLE  DD_DEV_ENV.BARRING;
...........
......
......
 .export reset;
 .Logoff;
.Quit;

The final exported file "Generated_DDL.sql" will have all the Create table statements which you can modify /parametrized and use to migrate to any other environment.

Thanks