What is the easiest way to empty all tables of my database?
in [Programming SQL]
|
From: Author on 2 Nov 2009 17:09 I have small database of a little more than 2 dozens of tables. For some test, I need to empty all tables. Because of the crisscross references that exist amongst the tables, I am having a hard time empty my tables. I know I can empty them one by one by starting from the very bottom of the reference tree and gradually clear the references. I am just wondering if there is an easier way, maybe some method that will force the emptying of a table regardless of its references? Or maybe go through the hassle of altering all tables and enable on delete cascade? Thank you very much in advance.
From: David Portas on 2 Nov 2009 17:25 "Author" <gnewsgroup(a)gmail.com> wrote in message news:a0e124aa-d8ae-451d-8a6a-ebea8b91bade(a)d5g2000yqm.googlegroups.com... >I have small database of a little more than 2 dozens of tables. > > For some test, I need to empty all tables. Because of the crisscross > references that exist amongst the tables, I am having a hard time > empty my tables. > > I know I can empty them one by one by starting from the very bottom of > the reference tree and gradually clear the references. > > I am just wondering if there is an easier way, maybe some method that > will force the emptying of a table regardless of its references? > > Or maybe go through the hassle of altering all tables and enable on > delete cascade? > > Thank you very much in advance. Execute the following a few times until no errors are returned. All rows in all tables should have been deleted. EXEC sp_msforeachtable 'DELETE FROM ?'; Use with care!! -- David Portas
From: Jeroen Mostert on 2 Nov 2009 17:44 David Portas wrote: > "Author" <gnewsgroup(a)gmail.com> wrote in message > news:a0e124aa-d8ae-451d-8a6a-ebea8b91bade(a)d5g2000yqm.googlegroups.com... >> I have small database of a little more than 2 dozens of tables. >> >> For some test, I need to empty all tables. Because of the crisscross >> references that exist amongst the tables, I am having a hard time >> empty my tables. >> >> I know I can empty them one by one by starting from the very bottom of >> the reference tree and gradually clear the references. >> >> I am just wondering if there is an easier way, maybe some method that >> will force the emptying of a table regardless of its references? >> >> Or maybe go through the hassle of altering all tables and enable on >> delete cascade? >> > > Execute the following a few times until no errors are returned. All rows in > all tables should have been deleted. > > EXEC sp_msforeachtable 'DELETE FROM ?'; > I'd slightly tweak that by executing EXEC sp_msforeachtable 'TRUNCATE TABLE ?'; first (only once), since truncating is easier on the transaction log than deleting. Of course, not all tables can be truncated, for those the regular DELETE still applies. This isn't the only way of achieving this, though. Another approach is to drop all foreign keys, truncate all the tables (which will be possible now as no more relationships exist), then recreate the keys. You can use Management Studio to produce the scripts by scripting only foreign keys and choosing the "script drop" and "script create" options, as necessary. Finally, in the case that you can't modify the original database and a backup would take too much time, you can script the database and all its objects and recreate the entire database as a blank slate. Whatever approach you choose, make sure to take a backup of the blank database so you don't need to do it again. -- J.
From: David Portas on 2 Nov 2009 17:56 "Jeroen Mostert" <jmostert(a)xs4all.nl> wrote in message news:4aef60e9$0$83232$e4fe514c(a)news.xs4all.nl... > > Finally, in the case that you can't modify the original database and a > backup would take too much time, you can script the database and all its > objects and recreate the entire database as a blank slate. > That is certainly the easiest way. If you are doing any kind of development it ought to be possible to build the database quickly from your scripts. Use a tool like Visual Studio Team System to do automated builds. -- David Portas
From: Author on 2 Nov 2009 21:12 On Nov 2, 5:44 pm, Jeroen Mostert <jmost...(a)xs4all.nl> wrote: > David Portas wrote: > > "Author" <gnewsgr...(a)gmail.com> wrote in message > >news:a0e124aa-d8ae-451d-8a6a-ebea8b91bade(a)d5g2000yqm.googlegroups.com... > >> I have small database of a little more than 2 dozens of tables. > > >> For some test, I need to empty all tables. Because of the crisscross > >> references that exist amongst the tables, I am having a hard time > >> empty my tables. > > >> I know I can empty them one by one by starting from the very bottom of > >> the reference tree and gradually clear the references. > > >> I am just wondering if there is an easier way, maybe some method that > >> will force the emptying of a table regardless of its references? > > >> Or maybe go through the hassle of altering all tables and enable on > >> delete cascade? > > > Execute the following a few times until no errors are returned. All rows in > > all tables should have been deleted. > > > EXEC sp_msforeachtable 'DELETE FROM ?'; > > I'd slightly tweak that by executing > > EXEC sp_msforeachtable 'TRUNCATE TABLE ?'; > > first (only once), since truncating is easier on the transaction log than > deleting. Of course, not all tables can be truncated, for those the regular > DELETE still applies. > > This isn't the only way of achieving this, though. Another approach is to > drop all foreign keys, truncate all the tables (which will be possible now > as no more relationships exist), then recreate the keys. You can use > Management Studio to produce the scripts by scripting only foreign keys and > choosing the "script drop" and "script create" options, as necessary. > > Finally, in the case that you can't modify the original database and a > backup would take too much time, you can script the database and all its > objects and recreate the entire database as a blank slate. > > Whatever approach you choose, make sure to take a backup of the blank > database so you don't need to do it again. > > -- > J. Fantastic. That's smart, using the last method you offered, it only took me 1 minute to create the db as a blank slate. I should have been aware of this method. Many thanks to the other suggestions, too. All good approaches.
|
Pages: 1 Prev: Transaction and resources Next: Using Multiple Pivot Keys |