How to delete all the data? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to delete all the data?

Author

Topic

markshen2006
Starting Member

15 Posts

Posted - 2008-04-21 : 11:23:50

Hi,

I want to delete all the data in all tables.

But I have question for this.

1. I do not know how to loop over all the tables and delete data.
2. the database have database diagram so there are dependency with tables so the delete order is hard to decide.

a solutions is:

I temporarily delete the diagram and I loop all the tables and delete all the data.My question is how to loop over all the tables?

Please give me a idea.

Thanks

Mark

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-21 : 11:30:46

use sp_msforeachtable procedure

exec sp_MSforeachtable
@command1 = 'DELETE FROM ?'

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-21 : 12:11:33

Less logging and faster: EXEC sp_MSforeachtable 'truncate table ''?'''

Terry

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 04:49:26

Refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 04:50:25

quote:
Originally posted by visakh16

use sp_msforeachtable procedure

exec sp_MSforeachtable
@command1 = 'DELETE FROM ?'

You need to take care of referential integrity
See my previous reply

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 05:47:05

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

use sp_msforeachtable procedure

exec sp_MSforeachtable
@command1 = 'DELETE FROM ?'

You need to take care of referential integrity
See my previous reply

Madhivanan

Failing to plan is Planning to fail

Yup i understood now
Thanks for the info

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-22 : 08:54:24

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

use sp_msforeachtable procedure

exec sp_MSforeachtable
@command1 = 'DELETE FROM ?'

You need to take care of referential integrity
See my previous reply

Madhivanan

Failing to plan is Planning to fail

Yup i understood now
Thanks for the info

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources