We often need to create Test Environments that are DDL structure copies of our Production Environment with a subset of data.
Copying the Views from production is a hassle because some view are built upon other views going up ot 5 layers deep.
They need to be created in a certain order.
Is it possile to somehow get the order in which to create the views?
I suggest you use Teradata ARC (Archive/Recovery) utility for that. When copying views with ARC, the dependencies will not be checked.
Just archive the views on one system and copy them to another using arcmain.
You must of course make sure that all the underlying objects of the views have been copied/created on the target system at some point, otherwise the views won't work.
The Copy/Create is done to that same Teradata System but different Database names.
PROD_NAMEZZZ.VIEW1 is turned into TEST_NAMEZZZ.VIEW1 in the process.
I see. You can change the databasename of the copied view with arcmain, but that does not solve the underlying views' and tables' issue.
This is just a speculation, but running "SHOW SELECT * FROM view;" seems to give all the underlying objects' DDLs in the right order. It would take some text parsing, but this might be a way to do it.
I may be terribly wrong on this one though, I don't think this is documented anywhere.
Produce a list of view names, ordered by CreateTimeStamp.
Embed the viewnames in a SHOW VIEW....; statement.
Export all views in a single export.
Do 'change all' of databasename.
Run on target system.