Make a loop to truncate 100 tables

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

SQL Server 2005 Forums

Transact-SQL (2005)

Make a loop to truncate 100 tables

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-01 : 11:28:18

I stored 100 tables' name in a table tToBeDeteled.
How to make a loop to truncate all 100 tables?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 11:37:46

or instead of a loop, create a dynamic sql and execute it in one go.

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-01 : 11:41:43

Can you tell me more about dynamic sql?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-01 : 11:46:23

DECLARE @sql varchar(1000)
SELECT @sql =
isnull(@sql,'') + replace('TRUNCATE TABLE [ ! ] ',' ! ', tblName)
from tToBeDeteled

EXEC(@sql)

Jim

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 11:46:23

read this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-i.aspx

but do not copy/paste and run in as it is on your database. understand what its doing and thne replace the INFORMATION_SCHEMA query to read table name from your tToBeDeleted.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 12:00:16

quote:
Originally posted by jimf

DECLARE @sql varchar(1000)
SELECT @sql =
isnull(@sql,'') + replace('TRUNCATE TABLE [ ! ] ',' ! ', tblName)
from tToBeDeteled

EXEC(@sql)

Jim

Because there are 100 tablenames I would prefer
DECLARE @sql varchar(max) instead of varchar(1000)

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-01 : 14:01:45

Thank you.
I convered it as store procedure as below but if change varchar(1000) to varchar(max) will get error.

ALTER PROCEDURE [dbo].[TruncateAllTables]
AS
DECLARE @sql varchar(max)
SELECT @sql =
isnull(@sql,'') + replace('TRUNCATE TABLE [ ! ] ',' ! ', tblName) from tToBeDeleted
EXEC(@sql)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 14:11:04

quote:
Originally posted by Sun Foster

Thank you.
I convered it as store procedure as below but if change varchar(1000) to varchar(max) will get error.

ALTER PROCEDURE [dbo].[TruncateAllTables]
AS
DECLARE @sql varchar(max)
SELECT @sql =
isnull(@sql,'') + replace('TRUNCATE TABLE [ ! ] ',' ! ', tblName) from tToBeDeleted
EXEC(@sql)

are you using sql 2005 with compatibility level 90? varchar(max) is only available from sql 2005 onwards

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 14:16:18

paste what this query returns

select @@version

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 14:18:03

also this to check if you're using correct compatoibility level

EXEC sp_dbcmptlevel 'Your database name'

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-01 : 14:22:47

only 80

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-07-01 : 14:24:30

and SQL 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 14:26:26

so you can use only varchar(8000)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 03:42:31

quote:
Originally posted by jimf

DECLARE @sql varchar(1000)
SELECT @sql =
isnull(@sql,'') + replace('TRUNCATE TABLE [ ! ] ',' ! ', tblName)
from tToBeDeteled

EXEC(@sql)

Jim

Child tables should be truncated first before parent tables
See these links
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-i.aspx
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-02 : 03:45:34

Also note this comment from rohitkumar

but do not copy/paste and run in as it is on your database. understand what its doing and thne replace the INFORMATION_SCHEMA query to read table name from your tToBeDeleted.

Madhivanan

Failing to plan is Planning to fail

a.rameshk
Starting Member

19 Posts

Posted - 2009-07-02 : 06:52:15

SET NOCOUNT ON
DECLARE @i INT
DECLARE @Count INT
DECLARE @SQL NVARCHAR(2000)
DECLARE @Name VARCHAR(200)
SET @i=1
SELECT @Count=COUNT(*) FROM tToBeDeteled
SELECT IDENTITY(INT,1,1)AS RowNum,* INTO Temp
FROM tToBeDeteled
WHILE (@i<@Count)
BEGIN

SET @i=@i+1
SELECT @Name=[Name]
FROM Temp
WHERE RowNum=@i
SET @SQL=' TRUNCATE TABLE '+@Name
--PRINT @SQL
EXEC sp_executesql @SQL
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 11:44:43

quote:
Originally posted by madhivanan

quote:
Originally posted by jimf

DECLARE @sql varchar(1000)
SELECT @sql =
isnull(@sql,'') + replace('TRUNCATE TABLE [ ! ] ',' ! ', tblName)
from tToBeDeteled

EXEC(@sql)

Jim

Child tables should be truncated first before parent tables
See these links
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-i.aspx
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-ii.aspx

Madhivanan

Failing to plan is Planning to fail

Also check for any foreign key constraints in tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources