Bookmark and Share BitCoin Donate: 13t8gAWVpHP2ddxMp88d1NFpZjnFJC6UwK

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