DELETE SQL Server 2000 records but keep database structure

[dbud]

I have a data source created in SQL Server 2000 running on WIndows Server 2003 which has served us just fine and is designed as needed. At this point we need to EMPTY IT OUT, i.e., delete ALL the existing records but keep the structure. When that has been achieved we plan to start repopulating it again via brute force data entry.

This seems simple enough but I cannot find the answer here in Tek-Tips or via Google searches of the WWW.

THANK YOU VERY MUCH FOR YOUR TIME & ASSISTANCE !

 

[gmmastros]

This may work well for you, but there is a potential problem with it.

[red]Caution: This may delete all your data[/red]

[tt][blue]sp_msforeachtable 'delete ?'[/blue][/tt]

I say may because if there is referential integrity set up on your tables, and this happens to delete from your tables in the wrong order, this may fail.

You could try running this repeatedly until are your tables are empty. Since you are deleting, all the transactions will be logged, so you will want to clear out your transaction log when you are done.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MasterRacker]

Keep in mind that even after you get all the data deleted, if you are using and identity fields you care about the actual number you would need to reseed those before beginning data entry.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[SQLDenis]

fastest way performance wise would be to script out all the tables, make sure you check the stuff as shown in the pic

also check generate drop statements

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

another way

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
  DELETE FROM ?
 else 
  TRUNCATE TABLE ?
'
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions