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