Delete all tables in SQL Server database except few

Question

I have around 50+ table in my database now what I want is drop all the tables in database except few.

now what I know is sys.tables is a table that list all tables so initially I ran a query like this

delete from sys.tables where name like '%DynamicSurgery' (or any other condition)

thinking that it might work. But as I expected it throws an error as

Ad hoc updates to system catalogs are not allowed.

Please tell me if there is a way to delete multiples in SQL Server?

Answer 1

You can use dynamic query to DROP the required tables:

DECLARE @ExecSQL AS NVARCHAR (MAX) = '';

SELECT @ExecSQL = @ExecSQL + 
    'DROP TABLE ' + QUOTENAME(S.name) + '.' + QUOTENAME(T.name) + '; ' 
FROM sys.tables T
JOIN sys.schemas S ON S.schema_id = T.schema_id
WHERE T.name LIKE '%DynamicSurgery'

--PRINT @ExecSQL
EXEC (@ExecSQL)

Answer 2

            EXEC sys.sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

            EXEC sp_MSforeachtable 'IF OBJECT_ID(''?'') NOT IN (
                  ISNULL(OBJECT_ID(''[dbo].[Table1]''),0),
                  ISNULL(OBJECT_ID(''[dbo].[Table2]''),0)
                  )
            DELETE FROM ?';

            EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL';

Answer 3

1. select the table by clicking on it
2. press the delete button and hit enter. Take note : if there is any dependencies(Foreign Key), the table will not be deleted