how to truncate data from multiple table at once

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

General SQL Server Forums

New to SQL Server Programming

how to truncate data from multiple table at once

Author

Topic

mr_dayal
Starting Member

37 Posts

Posted - 2008-06-28 : 02:46:43

Is There any way to delete / truncate data from all the tables of database in one pass insteed of truncating each table one by one.?

Mr Dayal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 02:50:57

Why do you want to do that?

mr_dayal
Starting Member

37 Posts

Posted - 2008-06-28 : 02:56:31

See I have many data which I was using for testing purpose, Not I want to start from begning.. There are almost 45 tables in my database.

Mr Dayal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 03:11:41

quote:
Originally posted by mr_dayal

See I have many data which I was using for testing purpose, Not I want to start from begning.. There are almost 45 tables in my database.

Mr Dayal

Ok then use undocumented stored procedure sp_Msforeachtable for this

it will be like

sp_Msforeachtable 'DELETE FROM ?'

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-28 : 03:17:39

easier to just restore from a backup that is in a known clean state I think.

sp_Msforeachtable 'DELETE FROM ?' won't work if you have FKs and you attempt to delete the primary first.

elsasoft.org

mr_dayal
Starting Member

37 Posts

Posted - 2008-06-28 : 03:26:44

great it worked ..
Thanks Visakh ...(may be your name should have been Vikash rahter Visakh)
Does it works on all the tables leaving one or two..
In other words is there any way to delete data from all the tables except for one or two tables?

Mr Dayal

mr_dayal
Starting Member

37 Posts

Posted - 2008-06-28 : 03:28:46

and yes Does it works If I have forign key retlation in my database

Mr Dayal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 03:34:20

quote:
Originally posted by mr_dayal

and yes Does it works If I have forign key retlation in my database

Mr Dayal

it wont work if you have foreign key constraints on other tables which refer to your tables data. in that case you need to first delete data from other table which refers your table and then delete the data from your table.

mr_dayal
Starting Member

37 Posts

Posted - 2008-06-28 : 03:37:54

ok and what about previous question .

Mr Dayal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 03:54:35

quote:
Originally posted by mr_dayal

ok and what about previous question .

Mr Dayal

sorry missed that. For that you need to include those table names inside if condition

sp_Msforeachtable 'IF ''?'' NOT IN(''YourTableName1'',''YourTableName2'',....) DELETE FROM ?'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-28 : 10:01:30

why not just script out the entire database and create a new one.

KH
[spoiler]Time is always against us[/spoiler]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-28 : 14:59:03

you can use Madhivanan script:

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-07-02 : 01:54:04

You could use TRUNCATE instead of delete , this is usually quicker

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-02 : 03:02:47

quote:
Originally posted by jackv

You could use TRUNCATE instead of delete , this is usually quicker

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Truncate will not work if there exists a foreign key

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 09:01:32

quote:
Originally posted by khtan

quote:
Originally posted by jackv

You could use TRUNCATE instead of delete , this is usually quicker

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

Truncate will not work if there exists a foreign key

KH
[spoiler]Time is always against us[/spoiler]

Refer the link that sodeep posted

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-02 : 09:03:46

quote:
Originally posted by visakh16

quote:
Originally posted by mr_dayal

ok and what about previous question .

Mr Dayal

sorry missed that. For that you need to include those table names inside if condition

sp_Msforeachtable 'IF ''?'' NOT IN(''YourTableName1'',''YourTableName2'',....) DELETE FROM ?'

Truncate is better option and your log file wont grow

Madhivanan

Failing to plan is Planning to fail

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-02 : 09:21:50

Am I invisible in this thread?