Oracle has a nifty utility called Define, which will tell you all about the tables and columns in the tables of your database. However, it will not run from inside OI XO connections, or at least I can't get it to. My program needs to be able to query the database itself to get a list of tables currently in the database. Revelation must know how to get this information, they use it for the dataset definitions window, but I can't find the correct system tables to query to get a list of tables, or a list of columns. Anyone know more then me? TIA

 Duane Pekse

 ALDATA Software

Duane,

I believe what you are looking for is the table SYSCOLUMNS. This contains column information for all of the tables in the database.

If you select distinct tablename from SYSCOLUMNS then that will give you all of the tables.

I could be slightly off on the tablename, but I'm pretty sure it's SYSCOLUMNS.

HTH,

msorrel@greyhound.com

Duane (and Matt)

If you look at sqlbatch.txt found in the oi directory you will see a sql batch (hence the name which needs to be run in sqlserver environment to make it work as nicely as Oracle (yeah!!!).

Referencing this you will get a pretty good handle on what OI is doing. Also .. use CSSpy (oi form executable) to see these processes. I *think* that this will show what the cs workspace commands are doing ..

dsig@teleport.com onmouseover=window.status=imagine … a company that supports it products with pride not extortion;return(true)"

David Tod Sigafoos ~ SigSolutions

cell: 503-341-2983

Sorry, I appear to have not been entirely clear in my problem. We currently use MS SQL Server, from which we get the database tables from SYSOBJECTS and the columns from SYSCOLUMNS (you were right Matt). However, Oracle system tables are called something different. They encapsulate this information into a function called DEFINE. I need to access the system tables directly, because DEFINE will not run from inside OI for some reason. I was hoping someone could tell me what the Oracle system tables were called.

 Duane Pekse

 ALDATA Software

from Oracle's Website

6.5 Retrieving Data from System Tables

Oracle Rdb stores information about the database as a set of tables called system tables, also called system relations. The system tables are the definitive source of Oracle Rdb metadata. Metadata defines the structure of the database; for example, metadata defines the fields that comprise a particular table and the fields that can index that table.

Querying system tables is another way, in addition to the SHOW statement, of finding information about the structure of the database. Using the SHOW statement in interactive SQL provides an easy method of finding information about the database structure. In fact, the SHOW statement itself queries the system tables automatically to obtain information about tables, views, indexes and so on.

You can query the system tables in an interactive session to obtain information that is not provided by the SHOW command. In programming, querying system tables may be the only way to capture certain information in the program.

Because system tables have a similar structure to tables of the real data, you can issue queries on the system tables in the same way that you do on any other table.

Every Oracle Rdb database that is created using the same version of the product has the same system tables, with the same columns.

Example 6-14 shows how to obtain a list of all system tables, and then how to obtain a more detailed description of one of them. The SHOW SYSTEM TABLES statement is similar to the SHOW TABLES statement used in Section 3.1 for listing user-defined tables.

---

CAUTION

While querying system tables for information is allowed, you should never attempt to add, delete, or change information in these tables.

---

Example 6-14 Querying a System Table

---

SQL] –

SQL] – List all system tables used in this version:

SQL] –

SQL] SHOW SYSTEM TABLES

System tables in database with filename mf_personnel

   RDB$COLLATIONS 

   RDB$CONSTRAINTS 

   RDB$CONSTRAINT_RELATIONS 

   RDB$DATABASE 

   RDB$FIELDS 

   RDB$FIELD_VERSIONS 

   RDB$INDEX_SEGMENTS 

   RDB$INDICES 

   RDB$INTERRELATIONS 

   RDB$MODULES 

   RDB$PARAMETERS 

   RDB$PRIVILEGES 

   RDB$QUERY_OUTLINES 

   RDB$RELATIONS 

   RDB$RELATION_CONSTRAINTS 

   RDB$RELATION_CONSTRAINT_FLDS 

   RDB$RELATION_FIELDS 

   RDB$ROUTINES 

   RDB$STORAGE_MAPS 

   RDB$STORAGE_MAP_AREAS 

   RDB$TRIGGERS 

   RDB$VIEW_RELATIONS 

   RDBVMS$COLLATIONS               A view. 

   RDBVMS$INTERRELATIONS           A view. 

   RDBVMS$PRIVILEGES               A view. 

   RDBVMS$RELATION_CONSTRAINTS     A view. 

   RDBVMS$RELATION_CONSTRAINT_FLDS A view. 

   RDBVMS$STORAGE_MAPS             A view. 

   RDBVMS$STORAGE_MAP_AREAS        A view. 

   RDBVMS$TRIGGERS                 A view. 

SQL] –

SQL] – Show the description of the RDB$VIEW_RELATIONS system table:

SQL] –

SQL] SHOW TABLE RDB$VIEW_RELATIONS

Information for table RDB$VIEW_RELATIONS

Columns for table RDB$VIEW_RELATIONS:

Column Name Data Type Domain

———– ——— ——

RDB$VIEW_NAME CHAR(31)

RDB$RELATION_NAME CHAR(31)

RDB$VIEW_CONTEXT INTEGER

 .

 .

 .

SQL] –

SQL] – Use RDB$VIEW_RELATIONS to find

SQL] – the tables or other views on which the CURRENT_INFO view is based:

SQL] –

SQL] SELECT RDB$RELATION_NAME

cont] FROM RDB$VIEW_RELATIONS

cont] WHERE RDB$VIEW_NAME=CURRENT_INFO'; (1)

RDB$RELATION_NAME

CURRENT_JOB (2)

DEPARTMENTS

JOBS

CURRENT_SALARY

4 rows selected

Hope this helps

Bob

View this thread on the forum...