1

I have a database which consist of 300 tables with data in it. I need to delete all the data inside each tables. I tried to truncate all tables but then I got an error that the process could not be completed because one of the column in a table is a foreign key. Is there other way to resolve my problem? Thanks.

sean
  • 9,198
  • 22
  • 65
  • 80
  • possible duplicate of [Temporarily disable all foreign key constraints](http://stackoverflow.com/questions/11639868/temporarily-disable-all-foreign-key-constraints) – Aaron Bertrand Aug 07 '12 at 02:46

2 Answers2

4

You need to either:

  • remove all the foreign keys, truncate, then re-create FKs;
  • disable all the foreign keys, delete (not truncate), then re-enable FKs; or,
  • delete from child tables first.

The latter may not be possible if you're lucky enough to have circular references, and it can still be complicated even without circular references. The first two are also relatively complex, but I solved a very similar problem for a different user recently (and I find these easier than trying to determine the proper delete order):

Temporarily disable all foreign key constraints

Another idea is to perform a simpler and more complete wipe:

  • script the tables (and other objects obviously), drop the database and re-create it; or,
  • create a copy of the database, and use Visual Studio / SSDT or a 3rd party schema comparison tool to create all of the objects in the empty database (then you can drop the old database and rename the new one).
Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • @Aaron I was thinking abt using MsForeachtable to delete the row and before doing that disable all the constraints .Wont that work ? – praveen Aug 07 '12 at 02:48
  • @praveen you won't be able to use `TRUNCATE` if you just disable the foreign keys, you'll have to use `DELETE`. – Aaron Bertrand Aug 07 '12 at 02:50
-1

Try this : A quick way of doing it .sp_msforeachtable is an undocumented SP so there's a risk in using them. I came up with this answer using Aaron Logic by disabling the constraints used in his answer.

use [YourDB]
Go

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'Truncate Table ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Community
  • 1
  • 1
praveen
  • 12,083
  • 1
  • 41
  • 49
  • 2
    sp_msforeachtable is dangerous when used this way for two reasons: (1) if a table is named with an apostrophe, space, reserved word or starts with a number, this will break (2) if you use any schemes other than dbo, this will break. – Aaron Bertrand Aug 07 '12 at 03:13
  • Thanks for pointing it out .Hence mentioned the risk in using them . – praveen Aug 07 '12 at 03:14
  • But these problems are not for sql server 2012 .They actually work with apostrophe, space, reserved word or starting with a number. with Previous version it might fail but i haven't tested it.But since the user has tagged 2008 ,so ur right with the issues – praveen Aug 07 '12 at 03:26
  • 2
    even if the user were on 2008 R2 or 2012, schema may still be an issue. And there are some highly unlikely but still possible table names that still aren't safe, e.g. `CREAT TABLE [foo[]]bar](x INT);` ... just because sp_MSforeachtable is simpler to write doesn't make it a good choice IMHO. – Aaron Bertrand Aug 07 '12 at 03:33
  • Seriously... im getting down voted just because i solved the problem as mentioned by SO which may have some pitfalls but it does solve the issue .Can any 1 pls explain fr de down vote?? – praveen Aug 07 '12 at 11:23