Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Truncate All Tables

Author  Topic 

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-28 : 09:59:19
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-12 : 07:38:41
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

22864 Posts

Posted - 2007-04-12 : 10:28:15
Thanks Peso

Madhivanan

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

pvsramu
Starting Member

17 Posts

Posted - 2007-06-13 : 11:38:52
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

22864 Posts

Posted - 2007-06-13 : 11:46:27
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 - 2007-07-23 : 12:32:32
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-23 : 12:52:23
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 - 2007-07-23 : 12:56:00
I understand that but..how shud i run this query without errors.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-23 : 13:01:19
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)

7020 Posts

Posted - 2007-07-23 : 14:32:04
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 - 2007-07-23 : 14:34:39
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

22864 Posts

Posted - 2007-07-30 : 03:53:29
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 - 2008-04-21 : 11:17:48
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

22864 Posts

Posted - 2008-04-22 : 04:53:41
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

1 Post

Posted - 2011-06-15 : 19:45:10
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

22864 Posts

Posted - 2011-06-20 : 10:25:12
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

12 Posts

Posted - 2011-10-27 : 08:15:58
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

22864 Posts

Posted - 2011-10-27 : 08:37:58
That's a good point Ravikumar. I have updated the post accordingly. Thanks
Go to Top of Page

cshooshirt
Starting Member

2 Posts

Posted - 2013-08-16 : 13:27:22
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 - 2013-08-16 : 13:52:29
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 Post

Posted - 2014-01-12 : 17:13:11
These scripts working well if table names without spaces.
Go to Top of Page
    Next Page

- Advertisement -