Dropping multiple tables in SQL Server 2005
I'm currently working on a project that has a Cache database that stores query results, and so naturally there are times when I'm playing inside of Management Studio that I want to delete the cache. However, I don't want to drop the entire database (because then I'd have to re-run the create script), nor do I want to delete all the tables in it (there is one table that needs to remain).
In case it's of use to anyone, or I need to do this again, here's a snippet of T-SQL that will drop an arbitrary list of tables from a database.
DECLARE @id varchar(255)
DECLARE @dropCommand varchar(255) DECLARE tableCursor CURSOR FOR
SELECT name FROM sys.tables WHERE name <> 'CacheManager' OPEN tableCursor
FETCH next FROM tableCursor INTO @id WHILE @@fetch_status=0
BEGIN
SET @dropcommand = N'drop table ' + @id
EXECUTE(@dropcommand)
FETCH next FROM tableCursor INTO @id
END CLOSE tableCursor
DEALLOCATE tableCursor
I wish there was a 'Copy as HTML' function or something similar I could use to get the nicely formatted text from Management Studio into a blog posting.