Post reply

delete all records from a database

  • 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

  • August 1, 2006 at 10:31 am

    #652599

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

    Andreas

  • August 1, 2006 at 11:01 am

    #652609

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

  • August 2, 2006 at 4:45 am

    #652772

    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]

  • August 2, 2006 at 7:05 am

    #652794

    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

  • August 2, 2006 at 7:16 am

    #652798

    Another option...

    sp_msforeachtable "truncate table ?"

     

    VERIFY THE LIST FIRST

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

    -- Cory

  • August 2, 2006 at 7:49 am

    #652805

    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

     

  • August 2, 2006 at 7:51 am

    #652807

    truncate table is faster.

  • August 3, 2006 at 12:41 am

    #653000

    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

  • August 3, 2006 at 7:03 am

    #653072

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

    Tom

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

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