How to delete all data in SQL database?

[FreeriderUK]

I found the following query and tried to execute in Management Studio, but has a problem with one of the tables:

 -- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO

Cannot truncate table 'dbo.Jobs' because it is being referenced by a FOREIGN KEY constraint.

I want to be able to delete all the data in all tables within my program.
I thought this would work, since it disables the constraints.

Why doesn't it work?
Is there a better way?

 

[Arg81]

it sounds like you have a relationship on the tables. Remember you cant delete parent data without deleting any child data first.

 

[FreeriderUK]

I thought NOCHECK CONSTRAINT ALL would prevent the need for that...

 

[FreeriderUK]

I found a solution:

/*********************************************************
Stored Procedure: usp_FlushRecords_AllTables
Creation Date: 07/24/2006
Written by: Vadivel Mohanakrishnan

Purpose: Delete ALL records within ALL the tables in a DB with ease.
Test: Exec usp_FlushRecords_AllTables **********************************************************/

Set nocount on

Exec sp_MSForEachTable 'Alter Table ? NoCheck Constraint All'

Exec sp_MSForEachTable
'
If ObjectProperty(Object_ID(''?''), ''TableHasForeignRef'')=1
Begin
-- Just to know what all table used delete syntax.
Print ''Delete from '' + ''?''
Delete From ?
End
Else
Begin
-- Just to know what all table used Truncate syntax.
Print ''Truncate Table '' + ''?''
Truncate Table ?
End
'

Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'

 

[Arg81]

that's the one - foreign reference....think that's what is used for the relationships, not constraints.
At least you found a way to do it

 

[cjard]

I want to be able to delete all the data in all tables within my program.
I thought this would work, since it disables the constraints.

Why doesn't it work?

I dont think you can call those kind of scripts from an ADO connection, but I may be wrong

Is there a better way?

Er, yeah.. youre supposed to have a development clean version of a database that has no data in it. All your testing rubbish is supposed to go into a copy of that DB..

For a relevant discussion, read the DNU link in my signature; its aimed at people who come here saying "why doesnt my UPDATE statement work" but when you read the expl of why "it doesnt work" then you'll know where you went wrong with your development clean room

 

[FreeriderUK]

I dont think you can call those kind of scripts from an ADO connection, but I may be wrong

I don't know either - I haven't tested it yet. But it works in Management Studio.

Er, yeah.. youre supposed to have a development clean version of a database that has no data in it. All your testing rubbish is supposed to go into a copy of that DB..

I do. But what about when a user wants to empty the database?

 

[cjard]

Just because they work there, i cant guarantee that they will work in normal connection experiences. Could be wrong, but there are faster ways of dumping a database (File.Copy a blank one over the top of it, yes you will thus have to distribute a clean empty database, but that's sensible.. Infact, make it so that it cheacks for the DB when it starts up, if not found, copies a clean version from a set location, then uses the copy.. ALl the user has to do to empty the db is delete it)

a) why would a user want to do that?
b) what's wrong with file/copy a clean copy over the full one?

 

[FreeriderUK]

read the DNU link...

I understand about copying to output directory - I'm not having a problem with that. But it has made me think about the fact I don't even have a local .mdf file.

Maybe I've missed something fundemental with SQLEXPRESS...

When I deploy, I assumed the database would be installed on the users machine under .\SQLEXPRESS

Are you saying it can be put in the Application folder and not need SQLEXPRESS installed?

a) why would a user want to do that?

Why wouldn't they? After trying the program with sample data, they might want to put 'real' data in.

b) what's wrong with file/copy a clean copy over the full one?

Nothing - if I can do it...

 

[cjard]

I understand about copying to output directory - I'm not having a problem with that. But it has made me think about the fact I don't even have a local .mdf file.

Maybe I've missed something fundemental with SQLEXPRESS...

When I deploy, I assumed the database would be installed on the users machine under .\SQLEXPRESS

Are you saying it can be put in the Application folder and not need SQLEXPRESS installed?

Think of SQLS as like a device driver. Without it installed, you wont be able to work with the MDF file. That's not to say it needs to be running, and indeed you shouldnt assume that it will be installed as a running service having the same instance name. If you have the MDF file listed as a datasource, then the machine ought to use whatever local instance of sqlsx is available

Why wouldn't they? After trying the program with sample data, they might want to put 'real' data in.

Interesting; i dont recall loading any sample documents into Microsoft Word before using it as a word processor.. So I guess the concept just, doesnt make that much sense to me?!

I guess you'd do what Microsoft would do; they dont supply one word document that you delete all the contents of when youre done wowing at the sample they have made.. THey provide a smaple, but you start with your own clean document based on an empty template

 

[FreeriderUK]

...you shouldnt assume that it will be installed as a running service having the same instance name.

Why not?

Interesting; i dont recall loading any sample documents into Microsoft Word before using it as a word processor.. So I guess the concept just, doesnt make that much sense to me?!

Interesting you would compare a 'complex' database app with Word.
When you open up Word, it's fairly ovbious what you need to do - even for someone who's never used it before.
A database app needs to be tested to see what it does with different data.

A database can't be compared to a simple WP - no matter how easy you think they are!