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.
| Author |
Topic |
|
madlo
Starting Member
41 Posts |
 Posted - 2011-04-21 : 03:44:51
|
| Hi,How do I truncate all tables in my databases but only the ones that end with the name _ArchiveCan I reuse sp_MSforeachtable somehow?e.g.EXEC sp_MSforeachtable @command1 = "TRUNCATE TABLE ?" |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-04-21 : 03:54:57
|
Try this:EXEC sp_MSforeachtable @command1 = "if '?' like '%[_]Archive' begin TRUNCATE TABLE ? end"
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
madlo
Starting Member
41 Posts |
Posted - 2011-04-21 : 07:07:24
|
| It didn't work.I also tried EXEC sp_MSforeachtable @command1 = "if '?' like '%Archive' begin TRUNCATE TABLE ? end"Get no error. It seems no tables match so it just says Command(s) completed successfully. |
|
|
|
madlo
Starting Member
41 Posts |
Posted - 2011-04-21 : 07:23:24
|
| It was doing a strange thing. Table ends in Archive but if it does not have wildcard at the end it does not work. EXEC sp_MSforeachtable @command1="IF '?' LIKE '%[_]Archive%'BEGINTruncate table ?END" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-21 : 07:42:55
|
| A little tweak to webfred's code:EXEC sp_MSforeachtable @command1 = 'if parsename(''?'',1) like ''%[_]Archive'' begin TRUNCATE TABLE ?; end'sp_MSforeachtable returns 2-part quoted names, like [dbo].[myTable_Archive]. The PARSENAME() function will extract only the part you're interested in. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-04-21 : 07:44:28
|
cool!thx
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-04-21 : 07:49:02
|
| This ins't clever, but it gives me moer confidence in what I'm doing.SELECT 'TRUNCATE TABLE ' + [name]FROM sys.tables WHERE [name] like '%_Archive'Then copy the output and run it in the query windowJimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
Truncate all tables ending with _Archive