Delete All 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 2000 Forums

Transact-SQL (2000)

Delete All Data

Author

Topic

wawansur
Starting Member

44 Posts

Posted - 2009-07-23 : 05:18:11

How to delete all data from database ?

Rgds

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 05:19:26

Truncate all the Tables !

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

wawansur
Starting Member

44 Posts

Posted - 2009-07-23 : 05:23:49

what is it truncate ?

Rgds

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-23 : 05:25:10

truncate is a T-SQL command.
Do you know bol (books online)?

No, you're never too old to Yak'n'Roll if you're too young to die.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 05:28:48

quote:
Originally posted by wawansur

what is it truncate ?

Rgds

Truncate is a T-SQL Statement

Syntax

Truncate table <tablename>

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

wawansur
Starting Member

44 Posts

Posted - 2009-07-23 : 05:30:06

could you give example ?
i have datebase with database name : dbIntegrated

Rgds

wawansur
Starting Member

44 Posts

Posted - 2009-07-23 : 05:33:13

I have 117 table in my databse.
Should I write this query one by one?

Rgds

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 05:34:51

Hey i mention in previous post,

U can truncate the tables only.
If u truncate all the table then automatically your DB became Empty.

so use this

Truncate table <tablename>

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 05:36:29

quote:
Originally posted by wawansur

I have 117 table in my databse.
Should I write this query one by one?

Rgds

Ya, else u can write a script and do the same with the help of While Loop

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 05:45:10

If you using MS-Sql 2005 then try this

EXEC sp_MSForEachTable
'BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 05:53:15

Truncate all the tables in a DB MS-Sql 2000

declare @SQL varchar(8000)

set @SQL = ''

select @SQL = @SQL + ' truncate table ' + name from sysobjects where type = 'U' and name <> 'dtproperties'

exec(@SQL)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

wawansur
Starting Member

44 Posts

Posted - 2009-07-23 : 06:10:03

ya, I want delete all data from all tabel in one query if could.

Rgds

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-23 : 06:16:56

quote:
Originally posted by wawansur

ya, I want delete all data from all tabel in one query if could.

Rgds

Ya choose the previous one depending on the version

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-23 : 06:27:49

quote:
Originally posted by wawansur

ya, I want delete all data from all tabel in one query if could.

Rgds

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

Madhivanan

Failing to plan is Planning to fail

wawansur
Starting Member

44 Posts

Posted - 2009-07-24 : 22:12:15

quote:
Originally posted by madhivanan

quote:
Originally posted by wawansur

ya, I want delete all data from all tabel in one query if could.

Rgds

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

Madhivanan

Failing to plan is Planning to fail

Thx Bro

Rgds

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-25 : 02:04:33

quote:
Originally posted by wawansur

quote:
Originally posted by madhivanan

quote:
Originally posted by wawansur

ya, I want delete all data from all tabel in one query if could.

Rgds

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

Madhivanan

Failing to plan is Planning to fail

Thx Bro

Rgds

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