delete all records from a database

Question

Post reply

delete all records from a database

Rui Barreira

SSC Rookie

Points: 31
More actions
August 1, 2006 at 7:42 am

#68336

Hi,
How can I easely delete all the records from a database?! th database is huge and if I can not get an automatic procedure I will take days... is there any automatica way of doing this?!

Kind Regards,

Rui Barreira

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andreas Schwarze Valued Member Points: 66 More actions · Answer 1

I'd script all of the db, drop the database and just create it new.

Andreas

Rui Barreira SSC Rookie Points: 31 More actions · Answer 2

Is there any chance I can create a clone of my database without the data?

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 3

Andreas has just given you a method for that just script it all out and use the script to create a new database.

Regards

Carolyn

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 4

DB ghost will do some of that scripting for you, but its got some limitations that are still working out.

you could use a cursor (nobody beat me with 'Cursors are bad'...we are deleting table data here not doing set operations).

The syntax may not be 100%, I dont have a db to actually RUN this on at the moment.

USE

DBNAME

GO

Declare

@owner varchar(256), @table varchar(256)

declare

tablename cursor fast_forward for

select

su.name as 'owner', so.name as 'table' from sysobjects so inner join sysusers su on so.uid=su.uid where XTYPE = 'U' order by su.name, so.name

open

tablename

fetch

next from tablename into @owner, @table

while

@@fetch_status = 0

BEGIN

EXEC

('TRUNCATE TABLE ' +@owner+'.'+@table)

fetch

next from tablename into @owner, @table

END

close

tablename

deallocate tablename

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

Another option...

sp_msforeachtable "truncate table ?"

VERIFY THE LIST FIRST

sp_msforeachtable "select '?', count(*) from ?"

-- Cory

Rui Barreira SSC Rookie Points: 31 More actions · Answer 6

To delete all the records in the database.

Here is a solution for my onde problems...

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'DELETE FROM ?'

GO

-- enable referential integrity again

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

It is not needed to delete and recreate the database, this stored procedure gets the job done

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 7

Andrew Stanton

Ten Centuries

Points: 1115

August 2, 2006 at 7:51 am

#652807

truncate table is faster.

Eugen-351194 Grasshopper Points: 23 More actions · Answer 8

made easy:

1step database (right click)>>all tasks>> generate sql script (for all database objects)

2.step delete database (or drop database from analyser)

3.step in analyser load the script and run it,

to make a copy.... export DTS wizard exists

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 9

Truncate table command. It is not a logged operation. Fast. No need to rescript entire database trying to get permissions etc done.

Tom