Truncate All Tables - Part I




This procedure will truncate all the tables in the database
To truncate all tables,I have seen the programmers using Cursors to get all tables and truncate them.This will avoid Cursors. Running this procedure will make SQL Server to slow down other tasks. So test this with test server and make sure that you are not running any other SQL Server related processes


 



Create Procedure Truncate_All_Tables

as

Declare @t table(query varchar(1000),tables varchar(50))


Insert into @t 

    select 'Truncate table ['+T.table_name+']', 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' 
 


Insert into @t 

    select 'delete table ['+T.table_name+']', 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' 


Declare @sql varchar(8000)


Select @sql=IsNull(@sql+' ','')+ query from @t


Exec(@sql)


GO


Run this procedure as


EXEC Truncate_All_Tables




 
Published 27 August 2007 16:23 by Madhivanan
Filed under: ,

Comments

# Truncate All Tables - Part II

26 December 2007 12:37 by Madhivanan

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