Deleting all data

Is there a way to delete all data from a database - all tables and all
tables excluding system tables?

sqlserver 2000?

Thanks,

TmuldTmuld (tmuldoon@.spliced.com) writes:

Quote:

Originally Posted by

Is there a way to delete all data from a database - all tables and all
tables excluding system tables?

A brute force method is to run

SELECT 'DELETE ' + name FROM sysobjects WHERE type = 'U'

Copy and paste the result into a query window, and the run it over and
over again, until there are no error messages. This presumes that the
only errors you get are from foreign keys. If you have triggers that
object to the deletion, you need to disable these.

Another alternative is build an empty database from scripts, either
taken from version control or generated by Enterprise Manager.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 26 Jul 2007 12:01:36 -0700, Tmuld wrote:

Quote:

Originally Posted by

>Is there a way to delete all data from a database - all tables and all
>tables excluding system tables?
>
>sqlserver 2000?

Hi Tmuld,

DROP DATABASE xxx
go
CREATE DATABASE xxx
-- add options here
go

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Hugo Kornelis <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in
news:p36ia39c3bhvcs5dfjsbluvphgus0nbqtu@.4ax.com:

Quote:

Originally Posted by

On Thu, 26 Jul 2007 12:01:36 -0700, Tmuld wrote:
>

Quote:

Originally Posted by

>>Is there a way to delete all data from a database - all tables and all
>>tables excluding system tables?
>>
>>sqlserver 2000?

>
Hi Tmuld,
>
DROP DATABASE xxx
go
CREATE DATABASE xxx
-- add options here
go
>

Might not be a bad idea to create a script to recreate the database and all
its objects before you start if you don't want to reinvent the wheel.