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.

Comments (3)
  1. TR says:

    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.

  2. Purushothama says:

    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

  3. Suraj says:

    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

Comments are closed.

Skip to main content