Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2002
    Posts
    192

    Unanswered: 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.

  2. #2
    Join Date
    Apr 2002
    Posts
    7
    Simply script the database then delete the database and create the database with the script.

  3. #3
    Join Date
    Mar 2002
    Posts
    192
    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.

  4. #4
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    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.
    Data Climber

  5. #5
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185

    This is what I do

    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.
    Last edited by sqlserver2k; 05-26-02 at 09:47.

  6. #6
    Join Date
    Oct 2001
    Posts
    18
    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

  7. #7
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    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
    Data Climber

  8. #8
    Join Date
    Mar 2002
    Posts
    192
    tnx sqlserver2k and megan. That's the answer that i was seeking.

  9. #9
    Join Date
    Oct 2001
    Posts
    18
    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.

  10. #10
    Join Date
    May 2002
    Location
    Shanghai,China
    Posts
    4
    /*
    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

  11. #11
    Join Date
    Jun 2002
    Location
    iceland
    Posts
    4
    TAKE CARE

  12. #12
    Join Date
    Feb 2002
    Posts
    7
    Select 'truncate table ' + name,* from Sysobjects
    where Type='u' order by name

    This is much faster than deleting data
    Prakash

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •