MS SQL Query to Delete ALL DATA

look up exec msforeachtable

http://smallworkarounds.blogspot.com/2009/02/script-to-delete-all-data-from-sql.html

http://www.devx.com/dbzone/Article/40967/1954

here u r

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
 
/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'
 
/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
 
/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

Select allOpen in new window

Try this Command on query Analyzer

EXEC sp_MSforeachtable @command1 = "DELETE FROM ?"

Remember this cannot be roll back

I assume i would use the DBNAME instead of the ?

Is that correct?

no,
you run the script as it is
you just run it in your DB from the query analyzer

dankangr,
     Your comment no 23792896 is the same as I posted in this question.
https://www.experts-exchange.com/questions/24175376/how-to-clear-all-records-in-db.html

introlux,
   << I assume i would use the DBNAME instead of the ? >>

No Need to use the DBName just

USE DBname in front of that script will suffice.

I am confused now. My DBName is called 'DBallstars'

WIll i just open new query and paste the following code:

EXEC sp_MSforeachtable @command1 = "DELETE FROM ?"

EXEC sp_MSforeachtable @command1 = "DELETE FROM DBallstars

rrjegan17
    What if the comment is the same? It was a useful comment and I used it, where is the problem?? You have share your comment on that question so i don't see the reason of not send it to others also. You should put a trade mark and all rights reserved if you don't want to use it. Or I should right comment posted by rrjegan17?
Really I can't see the problem here...

I get the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBallstars'.

I get more similar errors when executing

in QA

EXEC sp_MSforeachtable @command1 = 'DELETE FROM DBallstars'

use DBallstars

Select allOpen in new window

done the above and does not work

I get errors???????

Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBallstars'.

dankangr,
   I didn't meant that.
Instead if you have provided that Question itself, he could have gained more information right. That's my point and hence provided the link to him. Yes our codes are shared and if the same code would help others, then you can share the complete link so that the askers can refer more and gain more knowledge instead of us feeding them with the answers. Of course we can help but we need to help them more on the topic instead of just the answer alone.

Revert if you disagree with this.

introlux,
     I believe you are using SQL Server 2000 and installing SP 4 for SQL Server 2000 will fix this issue.

http://support.microsoft.com/kb/837970
http://sqlserver2000.databases.aspfaq.com/why-do-i-get-errors-about-master-spt-values.html

Ignore the Second link. Instead this should be the link

http://sqlserver2000.databases.aspfaq.com/why-do-i-get-object-could-not-be-found-or-invalid-object-name.html

I am using SQL Server 2005 btw

Regards,

introlux

Then try the one below:

Use DBName

DELETE FROM schemaname.tablename

Or try whether you are able to select your table by doing

Use DBName

SELECT * FROM schemaname.tablename

rrjegan17
   I didn't search for the link, I had coped the code i was interested in and I had keep it in a file on my PC.
So i think that we just have a big misunderstanding here. Never mind. We r here to help :)

Cheers ;-)

Ok.. No probs..
As we are here to help others, we can help them at an optimal level instead of mere answer. Cheers :-)