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, 0)

End

Set NoCount Off

quote:

---

Originally posted by supersql

ok.
I truncated all the data in my DB using

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

but still the space is not freed, more over it is occupying more space on the disk than before

---

quote:

---

Originally posted by markshen2006

I execute this in SQL Server 2005 and I get the following error:

Checking identity information: current identity value 'NULL', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Old'.
Checking identity information: current identity value '1', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Why?

Thanks

---

quote:

---

Originally posted by falux

Thanks, madhivanan. It still works, 6 years later on SQL Server 2008 R2 :-)

-------------------------
Alea ain't jacta just yet.

---

quote:

---

Originally posted by madhivanan

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 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

Madhivanan

Failing to plan is Planning to fail

---