Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Suggestions on automating "pruning" a database |
Thu, Nov 29 2012 9:26 AM | Permanent Link |
Adam Brett Orixa Systems | I have a database with tables & views.
Several of the views actually call other views to create Summaries. I want to grab some of the useful data from this database (about 5 tables only, no views) which I can then work with elsewhere. I need to do this for a number of systems, so I'm trying to figure out how to SCRIPT the whole process. Of course I should probably have put these tables in their own database first to avoid this! Hindsight is wonderful! -- My idea is to Backup the database, restore it with a new name & then DROP the bits I don't need. Something like this: PREPARE Stmt FROM 'SELECT Name FROM Information.Views '; OPEN Crsr; FETCH FIRST FROM Crsr ('Name') INTO FileName; WHILE NOT EOF(Crsr) DO EXECUTE IMMEDIATE ' DROP View "'+FileName+'" '; FETCH NEXT FROM Crsr('Name') INTO FileName; END WHILE; Is lovely as it DROPs all the VIEWs ... but it fails where a VIEW references other VIEWs. The first cannot be DROPPED until the other is. Is there a way around this? i.e. I can "ignore constraints" when I EMPTY TABLE, but not on a DROP ... is there some other way of ignoring constraints? |
Thu, Nov 29 2012 10:17 AM | Permanent Link |
David Cornelius Cornelius Concepts | You won't be able to have a short script that automatically does this
because of the dependencies you mention; you'll have to manually craft your own script so that it all happens in the specific order you specify. But as long as you're doing that, why not write a CREATION script instead of a DROP script? Do you need to copy the data also? Just write a script that creates all the tables and views that you need. -- David Cornelius Cornelius Concepts On 11/29/12 06:26, Adam Brett wrote: > I have a database with tables & views. > > Several of the views actually call other views to create Summaries. > > I want to grab some of the useful data from this database (about 5 tables only, no views) which I can then work with elsewhere. I need to do this for a number of systems, so I'm trying to figure out how to SCRIPT the whole process. > > Of course I should probably have put these tables in their own database first to avoid this! Hindsight is wonderful! > > -- > > My idea is to Backup the database, restore it with a new name & then DROP the bits I don't need. > > Something like this: > > PREPARE Stmt FROM > 'SELECT Name FROM Information.Views '; > > OPEN Crsr; > FETCH FIRST FROM Crsr ('Name') INTO FileName; > WHILE NOT EOF(Crsr) DO > EXECUTE IMMEDIATE > ' DROP View "'+FileName+'" '; > FETCH NEXT FROM Crsr('Name') INTO FileName; > END WHILE; > > > Is lovely as it DROPs all the VIEWs ... but it fails where a VIEW references other VIEWs. The first cannot be DROPPED until the other is. > > Is there a way around this? i.e. I can "ignore constraints" when I EMPTY TABLE, but not on a DROP ... is there some other way of ignoring constraints? > |
Fri, Nov 30 2012 4:20 AM | Permanent Link |
Adam Brett Orixa Systems | David
Thank you for this suggestion. I needed someone to kick start my thinking. I do need to copy the data (basically thousands of rowing of "settings" which are useful meta data for a new application) as well as the structure. However your post made me realise that I can EXPORT just the 2 tables I need and then IMPORT the data into the new DB. This leaves behind all the unnecessary configuration constraints etc. Stupid of me not to think of it. I was working from a BackUp file rather than a Database, so once it was Restored I didn't think to the step of exporting. |
Sat, Dec 1 2012 6:27 PM | Permanent Link |
David Cornelius Cornelius Concepts | Excellent! Glad I could, er, be of some sort of inspiration!
-- David Cornelius Cornelius Concepts On 11/30/12 01:20, Adam Brett wrote: > David > > Thank you for this suggestion. I needed someone to kick start my thinking. > > I do need to copy the data (basically thousands of rowing of "settings" which are useful meta data for a new application) as well as the structure. > > However your post made me realise that I can EXPORT just the 2 tables I need and then IMPORT the data into the new DB. This leaves behind all the unnecessary configuration constraints etc. > > Stupid of me not to think of it. I was working from a BackUp file rather than a Database, so once it was Restored I didn't think to the step of exporting. > |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |