Truncate all table in a database in sql server

As database administrators or developers, sometimes we need to perform an action on all of the tables within a database. Returns one row for each table in the current database for which the current user has permissions. To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA.view_name We are know that INFORMATION_SCHEMA.TABLES table has contain all TABLE_CATALOG ,

Set NoCount ON

Declare @tableName varchar(200)

set @tableName=''

While exists

(

--First of all getting all child table that has no any 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

)

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 table here

Exec('Truncate table '+@tableName)

set @TableName=''

While exists

(

--here fetch 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

)

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 all table here

Exec('Delete from '+@tableName)

--------now reset identity

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)

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Set NoCount Off