>From the FWIW file, in every database I create I include a "VIEW_NAMES" view that is very useful. I also define every database as an ODBC source.
File: VIEW_NAMES.VUE DROP VIEW VIEW_NAMES -- CREATE VIEW VIEW_NAMES (TBL_ID,TBL_NAME,COL_LIST,COL_QUERY) + AS SELECT T1.SYS_TABLE_ID,T2.SYS_TABLE_NAME,T1.SYS_COLUMN_LIST,T1. SYS_QUERY + FROM SYS_VIEWS T1,SYS_TABLES T2 + WHERE T1.SYS_TABLE_ID = T2.SYS_TABLE_ID -- RETURN Using this scenario when I unload/reload a database and I come up with missing view names I do this: 1. CONNECT to the rebuilt database. 2. SCONNECT to my previous "good" database (SCONNECT `MAINDATA`) 3. SATTACH the `VIEW_NAMES` view. (i.e. SATTACH `VIEW_NAMES` AS `MD_VIEW_NAMES` USING ALL) 4. And then use something like the following query: SELECT TBL_NAME FROM `MD_VIEW_NAMES` WHERE TBL_NAME NOT IN (SELECT TBL_NAME FROM `VIEW_NAMES`) This will give me any views that are in the backup database but not in the reloaded database. Assuming you keep a command file for each view you create, you can then simply run the command file and BOOM you're back in business. Another handy feature of VIEW_NAMES is that you can query the COL_QUERY column to search for views that contain a certain column or that references a particular table. Just thought I'd share. Have a great weekend! From: "Jim Belisle" <[email protected]> To: [email protected] (RBASE-L Mailing List) Date: 02/04/2011 08:36 AM Subject: [RBASE-L] - Re: Illegal table name Sent by: [email protected] Bill, Since I am not using the AFTER database (Razzak stated we should only use CLEAN databases) and since I run this process on another drive, I can do this over the weekend when I have time to implement your suggestion. Thanks for pointing me in the right direction. Hopefully I am a good detective. James Belisle From: [email protected] [mailto:[email protected]] On Behalf Of Bill Downall Sent: Friday, February 04, 2011 7:25 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Illegal table name On Fri, Feb 4, 2011 at 8:11 AM, Jim Belisle <[email protected]> wrote: Which tool would I use to find out the offending views? Jim, I'm assuming you have a "before" and an "after" copy of the database. Connect to "before". At R> Prompt, type these commands: OUTPUT ViewsBefore.TXT LIST VIEWS OUTPUT SCREEN (This gives you an alphabetical list of views from before the unload.) Connect to "after". At R> Prompt, type these commands: OUTPUT ViewsAfter.TXT LIST VIEWS OUTPUT SCREEN In notepad or RBEdit or printed on paper, compare the lists of views. When you find a view that did not survive the RELOAD, connect to "before" again, and type: LIST VIEW missingviewname Study the SELECT command, after the FROM keyword, to find the views that are referenced. That views that are referenced are now in the after database (unless they, also referred to views, not just tables.), because your original load ran all the way to completion. While still connected to "Before", build a little file that recreates this view: OUTPUT viewname.VIE UNLOAD STRUCTURE FOR viewname OUTPUT SCREEN Use RBEdit to remove the CREATE SCHEMA command from viewname.vie (which will refer to the "before" database name) Connect to the "after" database, and run the viewname.VIE file. Repeat this process for each missing view in the after database, but save the VIE files. You can just run them again after the next reload. Bill Confidentiality Notice This message is intended exclusively for the individual or entity to which it is addressed and may contain privileged, proprietary, or otherwise private information. If you are not the named addressee, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this message in error, please notify the sender immediately by e-mail and delete all copies of the message.
