Madhivanan

Truncate All Tables - Part II

There are many methods to truncate all the tables in a database
Part I of my post http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-i.aspx shows executing the concatenated truncate table
statements. This is other method

Set NoCount ON


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


While exists
( 
--Find all child tables and those which have 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