[RESOLVED] delete all data from all table

[shakti5385]

Hi all
I have a database in SQL SERVER 2005
that contaion 35 table
I want to clear all the table data using one query plese guide me!

I do not want to delete using while loop!

Thanks

[salvelinus]

I don't use SQL Server, but don't know of anyway to delete all from 35 tables in one query. Maybe there's a way to copy just the structure of the db to another one. What's the problem with using a loop?

[penagate]

Delete the database and then create it again. No loop required.

[Hack]

Or, rather than deleting the database, drop all the tables and recreate them.

My I ask what the issue with using a loop might be?

[Mark Gambo]

Create a Script that will drop and then recreate the tables. Don't forget about indexes and primary keys.

[szlamany]

How about just...

Truncate Table SomeTableA; Truncate Table SomeTableB; Truncate Table Sometable C;...

and so on

If that's a problem for you then create a stored procedure called TruncateAllMyTables and put the 35 truncate statements in that SPROC - and just execute it.

If you have lots of referential integrity between tables you might need to use DELETE FROM SOMETABLEA instead - but of course that is lots and lots slower and use the log file (which will expand it greatly).

[GaryMazzone]

Wouldn't Truncate have issues if there are FK relationships to of the table? I know that Oracle will not allow a tracate if the table is a Parent in a FK relationship.

[szlamany]

That is what I meant by...

If you have lots of referential integrity between tables you might need to use DELETE FROM SOMETABLEA instead - but of course that is lots and lots slower and use the log file (which will expand it greatly).

[shakti5385]

How about just...

Truncate Table SomeTableA; Truncate Table SomeTableB; Truncate Table Sometable C;...

and so on

If that's a problem for you then create a stored procedure called TruncateAllMyTables and put the 35 truncate statements in that SPROC - and just execute it.

If you have lots of referential integrity between tables you might need to use DELETE FROM SOMETABLEA instead - but of course that is lots and lots slower and use the log file (which will expand it greatly).

Thanks sir for guidence
I want to ask what is best to do?

Truncate or delete statement??

[GaryMazzone]

If there is referential integrity between tables then you must use Delete on the Parent side of the relationship. Truncate on any other table.

[szlamany]

As Gary said - TRUNCATE will fail with an error if there are foreign keys and what not.

Try TRUNCATE TABLE - if it fails - change it do DELETE FROM.

[shakti5385]

Thanks for guiding