Using such a CURSOR, you can print or execute all the DROP statements:
DECLARE cur_del CURSOR FOR
WHERE name NOT IN ('x', 'y', 'z', ...);
DECLARE @table sysname;
DECLARE @sql nvarchar(max);
FETCH NEXT FROM cur_del
WHILE @@FETCH_STATUS = 0
SET @sql = 'DROP TABLE ' + @table + ';';
--EXEC sp_executesql @sql;
FETCH NEXT FROM cur_del INTO @table
This query only prints the DROP statements out. You can uncomment the EXEC line if you want DROP them.
SELECT does not look for anything at all aside from what is included in the
IN(...) and you have to make sure it does not match more table than what you really want to delete.
In the WHERE clause, you can replace
name (table name) by the fully qualify name with schema using
QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) NOT IN ('schema.x', ...).
If there is only one schema, you can add
AND SCHEMA_NAME(schema_id) = 'xxx' and keep
It also does not look for dependencies which may prevent DROP.
If there are Foreign Keys, you can first DROP them using a similar cursor along with this query:
SELECT 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(f.schema_id))+'.'+ QUOTENAME(OBJECT_NAME(o.object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(f.name)
FROM sys.foreign_keys f
INNER JOIN sys.objects o ON f.parent_object_id = o.object_id
WHERE OBJECT_NAME(f.referenced_object_id) NOT IN ('x', 'y', 'z')
AND OBJECT_NAME(o.object_id) NOT IN ('x', 'y', 'z');