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
 
)
 
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 table here
 
Exec('Truncate table '+@tableName)
 
End
 
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
 
)
 
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 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)
 
End
 
-- disable referential integrity
 
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
 
GO
 
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
 
GO
 
-- enable referential integrity again
 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
 
GO
 
Set NoCount Off