Welcome to MSDN Blogs Sign in | Join | Help
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.

Posted: Wednesday, September 19, 2007 7:14 AM by casper
Filed under:

Comments

TR said:

Thank you - this was exactly what I needed.  I actually needed to drop all the tables, but I didn't want to mess up the permissions or schema associated with the DB.  Worked great.

# June 28, 2008 3:17 PM

Purushothama said:

Hi

I used the above to drop tables having common prefix like 'GPN'

DECLARE @id varchar(255) DECLARE @dropCommand varchar(255) DECLARE tableCursor CURSOR FOR     SELECT name FROM sys.tables WHERE NAME LIKE 'GPN%' 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

The point is this works in SQL2005 but now in SQL2000

In SQL 2000 we get errors like ' dropcommand' variable not declared.

Please let me know how i can solve this.

Purushothama

# November 21, 2008 4:50 AM

Suraj said:

I did a minor modification to the script. Running this script will give you ythe list of drop table statements, which can be reviewed/executed at one go.

Note: this is for SQL2000

DECLARE @id1 varchar(255),@dropCommand varchar(255)

DECLARE tableCursor CURSOR FOR SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE

TABLE_TYPE='BASE TABLE' and table_name like 'GPN%'

OPEN tableCursor FETCH next FROM tableCursor INTO @id1 WHILE @@fetch_status=0

BEGIN

print 'drop table ' +@id1

FETCH next FROM tableCursor INTO @id1

END

CLOSE tableCursor

DEALLOCATE tableCursor

# December 10, 2008 12:00 AM
Leave a Comment

(required) 

(required) 

(optional)

(required) 

  
Enter Code Here: Required

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Page view tracker