Post reply

truncating around constraints

  • May 8, 2002 at 12:02 pm

    #357534

    I am trying to truncate tables with constraints.

    I know the nocheck option for future updates.

    I know the command to drop a constraint but I have hundreds of constraints and keys.

    -K

  • May 8, 2002 at 1:37 pm

    #428737

    From MSDN:

    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

    TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.

    You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

  • May 8, 2002 at 2:56 pm

    #428741

    Although I don't suggest doing it for the sake of data consistancy you could try using.

    ALTER TABLE tblX NOCHECK CONSTRAINT all

    ALTER TABLE tblX DISABLE TRIGGER all

    then do your truncate and

    ALTER TABLE tblX CHECK CONSTRAINT all

    ALTER TABLE tblX ENABLE TRIGGER all

    If you need to truncate all the table you could do like so.

    sp_MsForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT all

    ALTER TABLE ? DISABLE TRIGGER all'

    GO

    sp_MsForEachTable 'Truncate Table ?'

    GO

    sp_MsForEachTable 'ALTER TABLE ? CHECK CONSTRAINT all

    ALTER TABLE ? ENABLE TRIGGER all'

    GO

    Hope this helps.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 3 posts - 1 through 2 (of 2 total)

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