truncate all tables in the database

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)

truncate all tables in the database

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-19 : 13:20:39

Hello,

Is there a way I can truncate all tables in my database?

Is this something I could do on a hosting account using a shared sql server or I need higher permissions to do this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-19 : 14:08:36

disgruntled employee?

One way is to generate the statements then paste the output into a new window and execute it.


use <myDatabase>
print 'use <myDatabase>'
select 'truncate table ' + table_name from information_schema.tables where table_type = 'base table'

permission to do this depenends on the permissions granted your "hosting account". Foreign Key constraints on the tables may prevent you from performing TRUNCATEs. If that is the case you can change to DELETES as long as you arrange the tables in the correct order so that child tables are deleted before parent tables.

Be One with the Optimizer
TG

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-02-19 : 15:51:43

>>disgruntled employee?
haha..no! I'm building this web app and I sometimes make a mistake in my DATA init script and I need to truncate all the tables, right now I just create a new db but thats obviously a pain.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-19 : 16:03:21

This might help.

Truncate All Tables:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Use at your own risk, and make sure you are in the correct database.

CODO ERGO SUM

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-20 : 01:29:58

Hi,

Try this also

Select 'Truncate Table '+ Name + '' From sys.sysobjects where Type = 'U'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-25 : 06:27:09

quote:
Originally posted by ranganath

Hi,

Try this also

Select 'Truncate Table '+ Name + '' From sys.sysobjects where Type = 'U'

That wont work properly. See the link that MVJ posted

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-25 : 07:54:38

quote:
Originally posted by ranganath

Hi,

Try this also

Exec 'Truncate Table '+ Name + '' From sys.sysobjects where Type = 'U'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-25 : 08:39:53

quote:
Originally posted by ranganath

quote:
Originally posted by ranganath

Hi,

Try this also

Exec 'Truncate Table '+ Name + '' From sys.sysobjects where Type = 'U'

Do you have any specific comment?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources