Drop schemas and tables automatically

I have a SQL Server database with more than 50000 schemas.

I need to keep 500 of them.
I try to drop all unnecessary schema but it's very long.
I have to delete all tables of one schema and then the schema itself.

Is there a possibility to do this automatically?

For the moment I use something like this to drop all tables

DECLARE @sql nvarchar(max) = '';
SELECT @sql = @sql + 'DROP TABLE dbo.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
WHERE t.type = 'U' AND t.schema_id = SCHEMA_ID('dbo');
EXEC sp_executesql @sql;

you can use this method to expedite what you want to do, whether you should be doing it is up to you to manage the risk. Depending on factors like table sizes, logging level, disk space, etc you could fill up the database transaction log quickly by dropping a lot of objects.

1. Test on a replica database first
2. Database backup!
3. Verify database backup can be restored!
4. Create a working table with the schema names that you want to delete. You can select them from sys.schemas if you have a naming standard to follow that allows you to identify the schemas to delete. This is dangerous work if you accidentally include schemas that shouldn't be deleted. You might want to do it in smaller batches rather than kick off a drop of 50,000 schemas.

CREATE TABLE [DropSchemaList](
    [SchemaName] [nvarchar](128) NOT NULL
);

INSERT INTO DropSchemaList
SELECT s.name
FROM sys.schemas s
WHERE s.name like '%somepattern%';

5. Create a cursor and iterate through the schema names that you inserted into the working table, dropping the tables and then finally dropping the schema. If there are objects other than tables you will need to drop those as well.

DECLARE @sql NVARCHAR(MAX);

-- Cursor definition to loop through schemas to drop
DECLARE SchemaCursor CURSOR FAST_FORWARD FOR
SELECT SchemaName
FROM DropSchemaList;

DECLARE @SchemaName NVARCHAR(128);

OPEN SchemaCursor;
FETCH NEXT FROM SchemaCursor INTO @SchemaName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N'';
    
    --Create drop statements
    SELECT @sql += 'DROP TABLE [' + s.name + '].[' + t.name + ']; '
    FROM sys.tables AS t
    JOIN sys.schemas AS s ON t.schema_id = s.schema_id
    WHERE s.name = @SchemaName;

    --Run drop statements
    IF (@sql <> '')
    BEGIN
        PRINT 'executing table drop for schema: ' + @SchemaName;
        EXEC sp_executesql @sql;
    END

    -- Drop the schema
    SET @sql = N'DROP SCHEMA [' + @SchemaName + '];';
    PRINT 'executing schema drop for schema: ' + @SchemaName;
    EXEC sp_executesql @sql;

    FETCH NEXT FROM SchemaCursor INTO @SchemaName;
END

CLOSE SchemaCursor;
DEALLOCATE SchemaCursor;