trucate all tables - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

trucate all tables

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2005-11-11 : 19:12:52

After test, I need insert real data in about 50 tables. How to code to trucate 50 tables?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-11 : 19:17:29

exec sp_MSforeachtable N"truncate table ? where ? <> 'dtproperties'"

use begin tran ... rollback for testing purposes.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-11-12 : 05:49:39

I use this to make a script that I check, remove some tables from

, and then run



select 'TRUNCATE TABLE ' + db_name() + '.dbo.[' + name + ']',
	CHAR(13)+CHAR(10)+'GO'
from	sysobjects 
where	type = 'u' 
	and name not in ('dtproperties')	-- System tables
--	and name not in ('XXX', 'YYY')		-- "Do not delete" tables
--	and name not like '%y%' ESCAPE '\'	-- Other "wildcard" names to NOT delete
order by [NAME]

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-14 : 00:20:50

Here is one more method
http://www.extremeexperts.com/SQL/Scripts/TruncateAllTables.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources