[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]] ==== Oracle DEFINE command defined (OpenInsight Specific) ==== === At 05 APR 2000 07:28:24PM Duane ALDATA wrote: === {{tag>"OpenInsight Specific"}} 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 ---- === At 06 APR 2000 08:57AM Matt Sorrell wrote: === 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 ---- === At 06 APR 2000 11:02AM dsig@teleport.com wrote: === 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 ---- === At 06 APR 2000 02:01PM Duane ALDATA wrote: === 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 ---- === At 10 APR 2000 02:35PM Bob Carten - WinWin Solutions wrote: === from [url=http://technet.oracle.com/doc/rdbnt8/htdocs/sqlintro/sqlintro_pro_advdatamanip.html]Oracle's Website[/url] 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 [[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=26F869F080CF28CB852568B80080F198|View this thread on the forum...]]