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



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

Comments

# re: Truncate All Tables - Part II

14 December 2007 14:07 by Phani prakash

when I tried to tuncate all the tables using ur script, I am not able to do it. Some Tables  throwing error like "Cannot truncate table 'Expenditure_Template' because it is being referenced by a FOREIGN KEY constraint."

# re: Truncate All Tables - Part II

14 December 2007 14:52 by Madhivanan

Hi Phani prakash

Which part did you use 1 or 2?

# re: Truncate All Tables - Part II

19 December 2007 11:40 by vmvadivel

Check out http://vadivel.blogspot.com/2006/07/easiest-fastest-way-to-delete-all.html

# re: Truncate All Tables - Part II

22 January 2008 13:18 by Ela

What happens if you have more than two tables in parent/child chain?

# truncate all tables | keyongtech

18 January 2009 16:31 by truncate all tables | keyongtech

Pingback from  truncate all tables | keyongtech