delete data from all tables.

Is there an easy way to delete all data from all nonsystem tables in a given sql server 2000 database?

Right now i have about 50 tables that i want to clear data out of and want to know if there is an easier way than

Delete from <mytable>

For each table i want to clear data out of.Simply script the database then delete the database and create the database with the script.|||is this script doable say in 3 to 4 lines. if so can you give me an example of how a delete all tables script might look like. Otherwise i think i'll just purge it the slow way for now.|||If you work with links from PK to FK, set the option 'Cascade DELETE' on each of them, then delete only parent tables... can still be boring!

But as suggested before, the best solution is to generate a script from your DB using DTS wizard, checking without data, and regenerate your DB by executing this script. It should take 5 minutes to do so.|||Use [Your Database]
Select 'delete from ' + name from Sysobjects
where Type='u' order by name

This will generate the statement:
Delete from A
Delete from B
Delete from C
Delete from D

Copy the Output to the Query Analyzer and press F5.It will start deleting the data from the user table in one go.

Also You might need to change the order of the delete for the Child and the Parent table(if the Relationship exists).Make sure that the Delete from Child is the statement before Delete from the Parent.

You can also replace the 'Delete from ' statement with the 'Truncate Table ' statement as it will be faster.

Hope it Helps.|||As per sqlserver2k's post, we use the same sort of thing:

select 'delete from ' + o.name + ';'
from sysobjects o,
sysusers u
where o.type = 'U'
and o.uid = u.uid
and upper(u.name) = 'xxx';

where 'xxx' is the owner of the object you're wanting to delete from.

Copy and paste the output back into the execution pane of whatever sql tool you're using and execute (f5).
You may need to run the execute several times if there are constraints, unless you've got the time to order the output so that it deletes in the right sequence for them (constraints, that is).

Also, take off the semi colon if you're using query analyzer :)|||TAKE CARE!

The method shown before (Select 'delete from ' + name from Sysobjects where Type='u' order by name) returns also dtproperties table, wich you might not want to delete. If so, add 'status>0' where clause, status coming from Sysobjects table.

You'll get:

Select 'delete from ' + name from Sysobjects where Type='u' and status>0 order by name|||tnx sqlserver2k and megan. That's the answer that i was seeking.|||No worries :)

BTW, Climber is right about dtproperties table. If the tables you want to delete from are owned by dbo, be a little careful and double-check the output from the select 'delete from' + query so that you're only executing delete statements on those tables you want to delete from.|||/*
If you work with links from PK to FK, set the option 'Cascade DELETE' on each of them, then delete only parent tables... can still be boring!
*/

declare @.sqlstring char(200)

DECLARE Tables_Cursor CURSOR FOR

SELECT 'truncate table '+ rtrim(name) FROM sysobjects where xtype='U' order by name

OPEN Tables_Cursor

FETCH NEXT FROM Tables_cursor INTO @.sqlstring

WHILE @.@.FETCH_STATUS = 0

BEGIN

exec(@.sqlstring)

FETCH NEXT FROM Tables_cursor INTO @.sqlstring

END

CLOSE tables_cursor

DEALLOCATE tables_cursor|||TAKE CARE|||Select 'truncate table ' + name,* from Sysobjects
where Type='u' order by name

This is much faster than deleting data
Prakash