August 24, 2009 at 7:03 am
An older version of software that I was using creates tmp...... tables in the SQL Database. Over a period it has created several hundred tables starting with tmp..... The new version I am using now does not have this problem.
How can I delete or drop these tables in one go. I have ensured that deleting these tmp tables does not affect the software functioning but deleting one by one is a pain. Any help with Query Analyser using the DELETE or some such function would be welcome. Thanks.
August 24, 2009 at 7:10 am
If you really want to get rid of them all at once, run this:
select 'drop table ' + name + ';'
from sys.tables
where name like 'tmp%';
That'll give you a list of drop commands. Copy and paste the results into a script window and run them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 24, 2009 at 8:58 am
I'd go with the solution from GSquared; it will work for you and it's easy to understand.
Test is on another db first, be sure it clears up what you want.
August 24, 2009 at 9:02 am
In management studio, press F7. Select all the tables that you want to delete, press the delete key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 24, 2009 at 9:08 am
GilaMonster (8/24/2009)
In management studio, press F7. Select all the tables that you want to delete, press the delete key.
Much easier, unless you're stuck with Enterprise Mangler, in which case go with GSquared.
August 24, 2009 at 12:32 pm
Thanks guys. Will try it out on a test DB first.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply