You can only run one DDL statement in a macro, so you cannot do this by macro.
Also, you do not need to collect the columnnames if you just want a Select * view.
If you run the following BTEQ script, it will set up a view in a viewsdb for every table in a data database:
------------------------------------------------------------------------
.Export Report File = CViews.sql
.Rtitle ''
.Foldline on
.Format Off
Select 'Replace View ViewsDb.V'||Tablename (Title '')
, 'As Select * From DataDB.'||Tablename (Title '')
, ';' (Title '')
From DBC.Tables
Where Databasename = 'DataDb'
Order By Tablename
;
.Export Reset
.Run File CViews.sql
-----------------------------------------------------
You will need to change the data and views databasenames in the script.
If you need the columnnames for some reason you can get them for DBC.Columns but you need to use Case to only get the Create and Select components on the first and last columns. Come back if you need that and cannot work it out.
Thank you for responding. I tries to run just the DDL created,but it did not return anything, I could just see teh column names as 'A' 'B' and 'C'. USer- View Db and Production is my tabel database.Am I Missing something here?
Select 'Replace View user.V'||Tablename (Title '')
, 'As Select * From production.'||Tablename (Title '')
, ';' (Title '')
From DBC.Tables
Where Databasename = 'production'
Order By Tablename
;
And yes, I need teh column names too as these views will be cerated in production.
Is your production database called Production or ProductionDB? The only reason I can see why it should return no rows is that database Production was empty or did not exist on the system you were using, or you are using the "X" views on your site and you do not have access to them.
It worked fine on my system, with relevant databasenames.
To get the columnnames as well, you need to run the following IN BTEQ!
------------------------------------------------------ ------------------
.Export Report File = CViews.sql
.Rtitle ''
.Foldline on
.Format Off
.Omit On 4,5
Select
CASE When ColNo = 1
THEN 'Replace View userView.V'||Tbl.Tablename||'As Select ('
Else '' END (Title '')
, Cols.Columnname (Title '')
, CASE WHEN RevColNo = 1 THEN ') From Production.'||Tbl.Tablename||';'
Else '' END (Title '')
, Row_Number () Over(Partition By Tbl.Tablename
Order By Cols.ColumnId) As ColNo
, Row_Number () Over(Partition By Tbl.Tablename
Order By Cols.ColumnId Desc) As RevColNo
From DBC.Tables Tbl
Join DBC.Columns Cols
On Tbl.Databasename = Cols.Databasename
And Tbl.Tablename = Cols.TableName
Where Tbl.Databasename = 'Production'
And Tbl.Tablekind = 'T'
Order By Tbl.Tablename, ColNo
;.Export Reset
.Run File CViews.sql
-----------------------------------------------------
Hi all!
I am trying to build around 300 views. The views are just a (Sel * from table) , but getting the column names from each table is pretty cumbersome. IS there a macro which will collect the column names for all teh tables and create DDL's for teh views?
Thank you!