Macro to create views?

Hello.  I just reversed engineered a database with 50 tables that contained no views.  I'm in need of a macro to bulk create views based on a selection of tables.  This is 1 to 1 relationship between table and view.  The macro would read the table and all it's columns and the view name would be the table name "_v" (example:  customer_v).

Thank you

Parents
  • Something like this should do it:

        Dim sViewDDL As String
        Dim vAnswer As Variant
        For Each oTable In oModel.Entities
            Set oView = oModel.Views.Add(0,0)
            oView.Name = "V_" & oTable.TableName
            oView.Owner = oTable.Owner
    
            sViewDDL = "CREATE OR REPLACE VIEW " & oView.Owner & "" & oView.Name & " AS " & vbNewLine & "SELECT "
            For Each oAttr In oTable.Attributes
                sViewDDL = sViewDDL & oAttr.ColumnName & ", "
            Next
            sViewDDL = Left(sViewDDL, Len(sViewDDL) - 2) & vbNewLine 'remove last comma and space
            sViewDDL = sViewDDL & "FROM " & oTable.Owner & "." & oTable.TableName
            vAnswer = oView.SetViewDDL (sViewDDL)
        Next
    

Reply Children
No Data