Once derived tables are created. How do you automatically create a listing of the SQL for all the derived tables. The option for SQL is not on the Options/Print tab. Is there a third party tool to do this? Or where is the SQL stored in the BOBJ system tables?
rlwwork (BOB member since 2012-01-30)
I don’t think you’ll find a CREATE TABLE command in the repository tables. But if you export to an ATL file you’ll get something close. Still, this is far from being a substitute for a good quality data modeling tool like PowerDesigner (by Sybase, an SAP company).
eganjp 
(BOB member since 2007-09-12)
Most database utilities like Oracle SQL Developer, SQL Server Management Studio etc have the capabilities to create such a script. Easier this way.
Werner Daehn 
(BOB member since 2004-12-17)
Thank you for your help, but I don’t think I explained the problem with enough detail.
The derived tables were created in Universe Designer XI 3.1 not in the supporting databases. Therefore SQL for the derived tables only exists in Designer. I was told by SAP that I would have to open each derived table and copy the SQL and paste it to a document. The SQL must be stored somewhere for Designer to retrieve it. I’m new to Designer, so I’m not familiar with how it stores universes.
Rhonda
rlwwork (BOB member since 2012-01-30)
I think you may need to post this question in one of the BOE forums since this is the Data Integrator/Data Services forum.
eganjp (BOB member since 2007-09-12)
For the .unv format you can use one of the SDK options provided in BOB’s Downloads area, or the universe compare utility hosted on my blog (link in signature) also extracts all of this information into XLS. For the new .unx format a scripted solution is not currently possible.
Moved your topic to the Semantic Layer forum for further discussion.
Dave Rathbun (BOB member since 2002-06-06)
Rhonda,
That seems to me the easiest way too.
It should be easy to find the derived tables in the structure pane. They should have a different colour. A good practice is to give derived tables a name like ‘DT_name’ so recognition is easy…
Hope this helps (a bit),
Johan
JdVries 
(BOB member since 2006-02-06)
Or go to the Tools menu and select List of Derived Tables. It provides a list of every derived table in the universe.
Dave Rathbun 
(BOB member since 2002-06-06)
Dave,
You’re absolutely right (of course).
I remember that a long time ago there was a problem with this function.
Something like exiting the Designer module without warning 
Never used it since then, but now it works great.
You can even see the aliasses on the derived tables 
Johan
JdVries (BOB member since 2006-02-06)