how to delete data from all the table 1

[nowayout]

hi all,

how can i delete all the data from all the table in database?

Thanks

 

[MAK9974]

1. script all the objects
2. drop the database
3. create database
4. run the script

 

[nowayout]

no i just want to delet all the data from database and then want to import from original database. is there othere way i can import the database which overwirtes the exsiting database?

 

[SQLSister]

If you want to log the transaction:

Delete from Tablename

if you do not want to log the transaction and you have admin rights

Truncate Table Tablename

From books online about truncate Table:
"You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger. TRUNCATE TABLE may not be used on tables participating in an indexed view."

Incidentally it's a good idea to make sure you have a fairly current backup before you delete whole tables worth of data. This is a bad time to find out that the database hasn't been backed up in three months. Many's the time someone tells someone to delete something and that person either deletes the wrong table's data by accident (oops) or the person requesting the delete changes his mind right after it was done or forgot about something else that would be affected by the delete and something breaks! The chances of something like this are higher, the longer it's been since your last backup. (See Murphy's Law!)

 

[SQLSister]

Oops didn't read what you wanted well enough. Ignore above. In DTS there is an option you can select that tells it whether you want to append or overwrite the data. When you select the source tables and views to import, click the transform button.

 

[nowayout]

ok now how can i drop all the table from one database i do know how to drop single table from database but i do want to drop all tables from database

Thanks,

 

[tlbroadbent]

The following will drop all user tables in the current database. Use it carefully.

exec sp_msforeachtable
'If Exists (Select * From sysobjects Where id=object_id(''?'') And type=''u'' And name<>''dtproperties'') drop table ?'
go


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Maximizer]

Without starting another thread, Terry is it possible to just 'delete' instead of 'drop' all tables? If so what mods are required.

Much appreciated!!

Thanks

James Keep, A.C.E.
Crystal Reports(tm) Certified Consultant 8.5 (CRCC)
CMRC
Crystal Decisions Business Partner
Montreal, Qc, Canada

http://www.cmrc.qc.ca

 

[tlbroadbent]

If the login is a system administrator or databse owner, use the following.

exec sp_msforeachtable
'If Exists (Select * From sysobjects Where id=object_id(''?'') And type=''u'' And name<>''[dbo].[dtproperties]'') Truncate Table ?'
go

If not SA or DBO, use DELETE in place of TRUNCATE Table.

exec sp_msforeachtable
'If Exists (Select * From sysobjects Where id=object_id(''?'') And type=''u'' And name<>''[dbo].[dtproperties]'') Delete ?'
go



If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[bont]

I see that you are writing some sort of code here. Where would you execute this code? Is it a function, procedure. How to do this?