-
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