Deleting all the values from all tables in a Database

Question

Post reply

Deleting all the values from all tables in a Database

raja_saminathan

SSCommitted

Points: 1607
More actions
April 14, 2008 at 12:00 am

#185322

Hi ,
Is there any Way to delete all the datas in all the tables of a database without reloading the database.
Expecting your valuable suggestions.Thanks in Advance for your kind co-operation
Best Regards,
Rajesh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Rafal S. created a script to get the job done.

See this link: http://www.sqlservercentral.com/scripts/Miscellaneous/62115/

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 2

use the buit in sp of sql server :

sp_foreachtable 'delete from ?'

it will delete the values from all the tables.

Thanks ,

Amit Gupta

(MCP)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

AMIT GUPTA (4/14/2008)
use the buit in sp of sql server :
sp_foreachtable 'delete from ?'
it will delete the values from all the tables.
Thanks ,
Amit Gupta
(MCP)

Not if you have foreign key constraints. Then you need to delete from "child" tables before you can delete from "parent" tables.

John

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Would not it be easier to just generate a script creating empty database?

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 5

I agree with Sergiy... life would be a lot easier that way... create a "Gold Set" of scripts or even an empty database to copy from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Why post the same question again when you got an answer here

http://www.sqlservercentral.com/Forums/Topic484231-8-1.aspx

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 7

How rude... I hate it when people cross post... wastes everyone's time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)