I am trying to create view definition from DBC tables below is the query whihc i have come up with, but the problem is listing out all the columns in the select list of the underlying table .
Can any one suggest how this can be achived.
You can get the col names from the dbc.columnsV joining with the DBC.TablesV.
Generate 2 Ranks.
1. based on Columnid in the asc order
2. based on Columnid in the desc order
Use a case statement to see if the asc rank is 1, then include 'Replace view Viewname locking row for access Select ' else, ','.
Similarly use another case to chk if the desc order rank is 1, then 'From DB Name.TBL Name;', else ''.
*Not able to cut n paste code from my Sql assistant. Sorry!*