Truncate All Tables

Today I read this Article that explains how to truncate all the tables in a Database. As sp_MSForEachTable is undocumented, I tried to do the same thing without using that




Set NoCount ON


Declare @tableName varchar(200)
set @tableName=''

While exists
(
--Find all child tables and those which has 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



Madhivanan

Failing to plan is Planning to fail

Thanks for share. It works.


Luis Martin
Moderator
SQL-Server-Performance.com

Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci

Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


All postings are provided “AS IS” with no warranties for accuracy.

Now, how did you manage to get those blank lines in between the code tags?

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

I dont know. After Set NoCount ON, the new lines are not displayed [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

As DBCC CHECKIDENT can only be executed by a sysadmin, who has access rights to all tables, I guess it will work on tables with different owners.

--
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de