BusinessObjects Board

How to list all database tables all universes based?

sdk development
#1

I have see the example of list database tables of one universe. I want to list all database tables of all universes. How can I achieve it by excel vba?
I used the code as follow:

 "SELECT TOP 1000000 SI_ID, SI_NAME, SI_TABLE FROM CI_APPOBJECTS " & _
        "Where SI_KIND='Universe' ORDER BY SI_NAME"

but the return value of SI_TABLE is just “1”, I don’t know why.

zuozuo1987 (BOB member since 2009-08-04)

#2

How can I write sql to get database tables?

zuozuo1987 (BOB member since 2009-08-04)

#3

Hello -

For this purpose, I would highly recommend reviewing and then implementing Dwayne Hoffpauir’s seminal article and solution titled:

Document a universe using Excel and the Designer SDK

I might first open the XLS and review each tab within the spreadsheet to get a sense of what types of metadata will be returned from the program.

Hope this helps.

Atul Chowdhury (BOB member since 2003-07-07)

#4

Thank you very much. I will take your advice.

zuozuo1987 (BOB member since 2009-08-04)

#5

But I still don’t know how to list databases of all universe together without choosing one after one. The classic case of Dwayne Hoffpauir is wonderful but it only get the information of the universe which I choose. I have more than 200 universe to document.

And I found that when I used

Select * From CI_APPOBJECTS where SI_KIND ='Universe'

There was no information about database but SI_TABLE. But its values is usually “0” or “1”. What is its meaning? Does that mean I cannot get the information through the method?

Thank you for any auggestions.

zuozuo1987 (BOB member since 2009-08-04)

#6

I don’t think u can get information on what database tables are used in the Universe from the CMS

AuguC (BOB member since 2009-11-20)

#7

You can’t do it in a single query, but you can write a small application to do it that will:

  1. Get the list of all of the universes.
  2. For each universe in the list, get the list of all of the tables.

You can then store the information in Excel or another format - for example, as a .NET programmer, I personally like to store the information in memory in a DataSet and then run a Crystal Report off of it to get what I need.

-Dell

hilfy :us: (BOB member since 2007-04-16)