SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Truncate All Tables
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 04/28/2006 :  09:59:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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, 0)

End

Set NoCount Off


Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 10/27/2011 08:38:48

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 04/12/2007 :  07:38:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Excellent work Madhi!

This has to be the father of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 04/12/2007 :  10:28:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Thanks Peso

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

pvsramu
Starting Member

USA
17 Posts

Posted - 06/13/2007 :  11:38:52  Show Profile  Reply with Quote
I modified your script little bit...




/*

This batch t-sql deletes data from all the tables in the database.

Here is what it does:
1) Disable all the constraints/triggers for all the tables
2) Delete the data for each child table & stand-alone table
3) Delete the data for all the parent tables
4) Reseed the identities of all tables to its initial value.
5) Enable all the constraints/triggers for all the tables.


Note: This is a batch t-sql code which does not create any object in database.
If any error occurs, re-run the code again. It does not use TRUNCATE statement to delete
the data and instead it uses DELETE statement. Using DELETE statement can increase the
size of the log file and hence used the CHECKPOINT statement to clear the log file after
every DELETE statement.

Imp: You may want to skip CHECKIDENT statement for all tables and manually do it yourself. To skip the CHECKIDENT,
set the variable @skipident to "YES" (By default, its set to "NO")

Usage: replace #database_name# with the database name (that you wanted to truncate) and just execute the script in query analyzer.

*/

use [#database_name#]

Set NoCount ON


Declare @tableName varchar(200)
Declare @tableOwner varchar(100)
Declare @skipident varchar(3)
Declare @identInitValue int

set @tableName = ''
set @tableOwner = ''
set @skipident = 'NO'
set @identInitValue=1

/*
Step 1: Disable all constraints
*/

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*
Step 2: Delete the data for all child tables & those which has no relations
*/

While exists
(
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 top 1 @tableOwner=T.table_schema,@tableName=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
order by t.table_name


--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)

--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

checkpoint
End

/*
Step 3: Delete the data for all Parent tables
*/

set @TableName=''
set @tableOwner=''

While exists
(
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 top 1 @tableOwner=T.table_schema,@tableName=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
order by t.table_name

--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)

--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

checkpoint

End

/*
Step 4: Enable all constraints
*/

exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

Set NoCount Off
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 06/13/2007 :  11:46:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Well. May I know why you avoided Truncate?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 07/23/2007 :  12:32:32  Show Profile  Reply with Quote
I get the following mesg for most of the tables when I run this query

Warning: The table empcontact' has been created but its maximum row size (17468) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Warning: The table 'empact' has been created but its maximum row size (17727) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Edited by - supersql on 07/23/2007 12:33:07
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/23/2007 :  12:52:23  Show Profile  Visit spirit1's Homepage  Reply with Quote
well that is a problem with your tables having columns that exceed 8060 bytes.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 07/23/2007 :  12:56:00  Show Profile  Reply with Quote
I understand that but..how shud i run this query without errors.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/23/2007 :  13:01:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
what you get are warnings not errors.
these warnings will always appear as long as you have a table which columns exceed the 8060 bytes limit.

what errors do you get?


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/23/2007 :  14:32:04  Show Profile  Reply with Quote
This has nothing to do with the script posted on this thread.

If you have a question, post it on one of the SQL forums.


CODO ERGO SUM
Go to Top of Page

supersql
Yak Posting Veteran

99 Posts

Posted - 07/23/2007 :  14:34:39  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 07/30/2007 :  03:53:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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


Read the script again. I didnt use sp_MSForEachTable

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

markshen2006
Starting Member

15 Posts

Posted - 04/21/2008 :  11:17:48  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 04/22/2008 :  04:53:41  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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


Refer this
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2173958&SiteID=1

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

falux
Starting Member

Thailand
1 Posts

Posted - 06/15/2011 :  19:45:10  Show Profile  Reply with Quote
Thanks, madhivanan. It still works, 6 years later on SQL Server 2008 R2 :-)

-------------------------
Alea ain't jacta just yet.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 06/20/2011 :  10:25:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by falux

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

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


Thanks for the feedback

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ravikumarsv
Starting Member

India
12 Posts

Posted - 10/27/2011 :  08:15:58  Show Profile  Reply with Quote
A small correction, in your below code "DBCC CHECKIDENT (@tableName, RESEED, 1)" has identity value of '1' which will generate the identity value '2' for the new record.

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

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 10/27/2011 :  08:37:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
That's a good point Ravikumar. I have updated the post accordingly. Thanks

Edited by - madhivanan on 10/27/2011 08:39:37
Go to Top of Page

cshooshirt
Starting Member

2 Posts

Posted - 08/16/2013 :  13:27:22  Show Profile  Reply with Quote
quote:
Originally posted by pvsramu

I modified your script little bit...




/*

This batch t-sql deletes data from all the tables in the database.

Here is what it does:
1) Disable all the constraints/triggers for all the tables
2) Delete the data for each child table & stand-alone table
3) Delete the data for all the parent tables
4) Reseed the identities of all tables to its initial value.
5) Enable all the constraints/triggers for all the tables.


Note: This is a batch t-sql code which does not create any object in database.
If any error occurs, re-run the code again. It does not use TRUNCATE statement to delete
the data and instead it uses DELETE statement. Using DELETE statement can increase the
size of the log file and hence used the CHECKPOINT statement to clear the log file after
every DELETE statement.

Imp: You may want to skip CHECKIDENT statement for all tables and manually do it yourself. To skip the CHECKIDENT,
set the variable @skipident to "YES" (By default, its set to "NO")

Usage: replace #database_name# with the database name (that you wanted to truncate) and just execute the script in query analyzer.

*/

use [#database_name#]

Set NoCount ON


Declare @tableName varchar(200)
Declare @tableOwner varchar(100)
Declare @skipident varchar(3)
Declare @identInitValue int

set @tableName = ''
set @tableOwner = ''
set @skipident = 'NO'
set @identInitValue=1

/*
Step 1: Disable all constraints
*/

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*
Step 2: Delete the data for all child tables & those which has no relations
*/

While exists
(
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 top 1 @tableOwner=T.table_schema,@tableName=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
order by t.table_name


--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)

--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

checkpoint
End

/*
Step 3: Delete the data for all Parent tables
*/

set @TableName=''
set @tableOwner=''

While exists
(
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 top 1 @tableOwner=T.table_schema,@tableName=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
order by t.table_name

--Delete the table
Exec('DELETE FROM '+ @tableOwner + '.' + @tableName)

--Reset identity column
If @skipident = 'NO'
If exists(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
begin
set @identInitValue=1
set @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
end

checkpoint

End

/*
Step 4: Enable all constraints
*/

exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

Set NoCount Off


Go to Top of Page

cshooshirt
Starting Member

2 Posts

Posted - 08/16/2013 :  13:52:29  Show Profile  Reply with Quote
Using SQL Server 2008 R2 express: I have been trying/looking for a way to automate a process that takes multiple tables from an Access database (downloaded from a website and updated weekly by a state agency)and imports them into SQL Server Express (testing environment); but I need to do this on a fairly regular basis (usually weekly) so I need to delete the data in the existing SQL Server tables before I import from Access.
So far so good; I tested pvsramu's code and voila! That tales care of deleting all of the existing data. There are no constraints on the tables because I built the views to pull together what is needed to link to another in house Access database which is where the users want to work with the data.
I know if I use the regular edition of SQL server I can save the import package; I am guessing I can save pvsramu's code as a script; can I somehow pull this all together into a neat little job?

Thanks in advance!
Go to Top of Page

LZ
Starting Member

1 Posts

Posted - 01/12/2014 :  17:13:11  Show Profile  Reply with Quote
These scripts working well if table names without spaces.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000