Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Truncate All Tables - Part II

Aug 27 2007 12:00AM by Madhivanan   

There are many methods to truncate all the tables in a database.

Part I of my post Truncate all tables shows executing the concatenated truncate table statements. This is other method

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

Tags: t-sql, sql_server, truncate_table,


Madhivanan
3 · 40% · 12958
3 Readers Liked this
Jacob Sebastian Liked this on 4/9/2012 6:53:00 AM
Profile · Blog · Facebook · Twitter
Madhivanan Liked this on 6/22/2012 12:53:00 AM
Profile · Blog · Facebook · Twitter
Dattatrey Sindol (Datta) Liked this on 6/22/2012 5:50:00 AM
Profile · Blog
3
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

3  Comments  

  • @Madhivanan - Looks like the link to the Part I of the article is broken. It is showing up as "http://beyondrelational.com/controlpanel/blogs/posteditor.aspx/%3Cpre%20class=%22brush:%20sql%22%3E"

    commented on Jun 22 2012 5:50AM
    Dattatrey Sindol (Datta)
    42 · 4% · 1336
  • Thanks Dattatrey I have corrected the link. Please check.

    commented on Jun 22 2012 6:01AM
    Madhivanan
    3 · 40% · 12958
  • Works fine now @Madhivanan. Thanks :-)

    commented on Jun 22 2012 6:27AM
    Dattatrey Sindol (Datta)
    42 · 4% · 1336

Your Comment


Sign Up or Login to post a comment.

"Truncate All Tables - Part II" rated 5 out of 5 by 3 readers
Truncate All Tables - Part II , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]