SQL Server Performance

Truncate All Tables

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, Apr 28, 2006.

  1. Madhivanan Moderator

    Today I read this Article that explains how to truncate all the tables in a Database. As sp_MSForEachTable is undocumented, I tried to do the same thing without using that




    Set NoCount ON


    Declare @tableName varchar(200)
    set @tableName=''

    While exists
    (
    --Find all child tables and those which has no relations

    select T.table_name from INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
    or TC.constraint_Type is NULL) and
    T.table_name not in ('dtproperties','sysconstraints','syssegments')
    and Table_type='BASE TABLE' and T.table_name > @TableName
    )


    Begin
    Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name where (TC.constraint_Type ='Foreign Key'
    or TC.constraint_Type is NULL) and
    T.table_name not in ('dtproperties','sysconstraints','syssegments')
    and Table_type='BASE TABLE' and T.table_name > @TableName

    --Truncate the table
    Exec('Truncate table '+@tableName)

    End

    set @TableName=''


    While exists
    (
    --Find all Parent tables

    select T.table_name from INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key'
    and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
    and T.table_name > @TableName
    )


    Begin
    Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T
    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key'
    and T.table_name <>'dtproperties'and Table_type='BASE TABLE'
    and T.table_name > @TableName

    --Delete the table
    Exec('Delete from '+@tableName)

    --Reset identity column
    If exists(
    SELECT * FROM information_schema.columns
    WHERE COLUMNPROPERTY(OBJECT_ID(
    QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
    column_name,'IsIdentity')=1
    )

    DBCC CHECKIDENT (@tableName, RESEED, 1)

    End

    Set NoCount Off



    Madhivanan

    Failing to plan is Planning to fail
  2. Luis Martin Moderator

    Thanks for share. It works.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  3. FrankKalis Moderator

  4. Madhivanan Moderator

    I dont know. After Set NoCount ON, the new lines are not displayed [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  5. pvsramu New Member

    what if there are objects owned by different users? will it work?
  6. FrankKalis Moderator

    As DBCC CHECKIDENT can only be executed by a sysadmin, who has access rights to all tables, I guess it will work on tables with different owners.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
    Webmaster:http://www.insidesql.de

Share This Page