Truncate all table's from a db

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

New to SQL Server Programming

Truncate all table's from a db

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-01-18 : 04:17:45

any idea why this is not wroking ?
------------------------------
declare
@TableName varchar

DECLARE
EmptyTables CURSOR FOR

Select Table_Name From Information_Schema.Tables
Where Table_Type = 'Base Table' Order By Table_Type

open EmptyTables

FETCH NEXT FROM
EmptyTables
into
@TableName

WHILE @@FETCH_STATUS = 0
BEGIN
truncate table @TableName

FETCH NEXT FROM
EmptyTables
into
@TableName

END

close EmptyTables
deallocate EmptyTables

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-18 : 04:23:30

u must use dynamic sql here. read sp_executesql in BOL.

This is the best site available for Dynamic SQL; refer this

http://www.sommarskog.se/dynamic_sql.html

Madhu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-18 : 04:23:52

Yo need to make use of dynamic sql.

declare ...
..
exec('truncate table ' + @TableName)
-- rest of the code

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-18 : 04:26:01

Also, if you have foreign key constraints referencing the tables, they will prevent the truncate table statement from running.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 04:48:09

[code]DECLARE @TableCount INT

SELECT @TableCount = COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'

WHILE @TableCount > 0
BEGIN
EXEC sp_msforeachtable 'DELETE FROM ?'

SELECT @TableCount = @TableCount - 1
END[/code]
Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-18 : 05:17:39

Peter,
What is the purpose of WHILE loop? Is it to repeatitively try and delete data from tables in case of foreign key conflicts?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 05:36:02

Yes, it is.

One table for every run is always "the last table" with no foreign keys attached (otherwise you will have a circular reference which is not allowed).
If you have three tables connected with PK/FK as

A -> B -> C

My code above fails (first loop) for table A and table B, but works for table C.
Next loop; fails for table A, works for table B and table C (already deleted so this table is very fast).
Next run (third and last); works for table A, table B and table C (B and C already deleted so these tables are very fast).

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-18 : 05:47:08

I guessed as much. Thanks Peter for the clean explaination.

But shouldn't we limit the count only to the number of tables which has foreign key or referenced by foreign key, for the purpose of avoiding unnecessary loop rounds?

SELECT	@TableCount = COUNT(*) + 1
FROM	INFORMATION_SCHEMA.TABLES
WHERE	OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0
        AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasForeignRef') = 1
	AND TABLE_TYPE = 'BASE TABLE'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 05:54:45

sp_msforeachtable doesn't know that.
Also, you can have tables with no references at all (like a settings table) and these tables' data should be deleted as well.

I just wrote the snippet above because I thought it was neat, small and clean. And it works.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-18 : 06:01:47

That's exactly my point. Let's we have 5 tables, out of which say, 3 table doesn't have any foreign key or not referenced by the foreign, one table has the foreign key and another table which is being referenced by that foreign key.

A - No F.K. or F.k. Ref
B - No F.K. or F.k. Ref
C - No F.K. or F.k. Ref
D - F.K. (references table E)
E - referenced by D

Now if we take count of all tables, it will loop for five times but if we take count of just those tables which are referenced by FK + 1,
we will loop only 2 times which is sufficient to delete data from all tables.

In 1st round, data in tables - A, B, C, E will be deleted
In 2nd round, data in table D will be deleted.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 06:19:41

I agree!
But the code will be much more complicated and longer.

And involve some logics for which of the tables D and E to delete first.
Here is some code for that http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 07:06:52

I must learn to read more proper. Now I see your point!

This is the improved version, thanks to Harsh.

DECLARE	@TableCount INT

SELECT	@TableCount = COUNT(*)
FROM	INFORMATION_SCHEMA.TABLES
WHERE	OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0
        AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasForeignRef') = 1
	AND TABLE_TYPE = 'BASE TABLE'

WHILE @TableCount >= 0
	BEGIN
		EXEC sp_msforeachtable 'DELETE FROM ?'

		SELECT	@TableCount = @TableCount - 1
	END

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources