CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Fearghal
Fearghal

Reputation: 11407

How to drop all tables except 1

I have a bunch of db tables and I want to drop all but one. My script is as follows....however I have created an infinite loop when I run my attempt..

Any ideas how i can do this?

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects 
                WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL 
IF @name !=  'tableNotToBeDropped'
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects  
                    WHERE [type] = 'U' AND category = 0 AND [name] > @name 
                    ORDER BY [name])
END
GO

Upvotes: 6

Views: 7872

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

There is no need for loop at all. DROP TABLE can drop multiple tables with one statement:

DECLARE @tables NVARCHAR(MAX) = 
         STUFF((SELECT ',' + QUOTENAME([table_name]) AS [text()]  
               FROM INFORMATION_SCHEMA.TABLES
               WHERE table_schema = 'dbo' 
               AND TABLE_NAME <> 'tableNotToBeDropped'
               FOR XML PATH('')),1,1,'');
               
DECLARE @sql NVARCHAR(MAX) = 'DROP TABLE ' + @tables;

-- debug
SELECT @sql;

EXEC sp_executesql @sql;

LiveDemo

How it works:

  1. Get all table names from specific schema except one
  2. Create comma separated list
  3. Drop tables

Keep in mind that if your tables have defined foreign keys the order of dropping matters.

EDIT:

SQL Server 2017 version:

DECLARE @sql NVARCHAR(MAX) = 'DROP TABLE IF EXISTS ' 
            + (SELECT string_agg(QUOTENAME(table_name), ',')
               FROM INFORMATION_SCHEMA.TABLES
               WHERE table_schema = 'dbo' 
               AND TABLE_NAME <> 'tableNotToBeDropped'
               AND TABLE_NAME LIKE 't%');

SELECT @sql;
EXEC sp_executesql @sql;

DBFiddle Demo

Upvotes: 12

Ross Bush
Ross Bush

Reputation: 15175

This one liner also works:

sp_MSforeachtable @command1='IF OBJECT_ID(''?'') NOT IN (OBJECT_ID(''TableToNotDelete'')) DROP TABLE ?'

Upvotes: 1

Joe C
Joe C

Reputation: 3993

I suggest you use a cursor instead and add the name not equals to the where clause of the cursor query.

http://www.kodyaz.com/articles/sql-cursor-example-tables-rows-count-using-sql-server-cursor.aspx

Upvotes: 0

Related Questions