How to drop all tables except 1

Question

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

Answer 1

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