SQL Server – Removing All Schemas and Objects Except Specific Ones

sql server

I have a database with three main Schemas;

  1. Global
  2. Permissions
  3. Unique Schema name based on the customers ID number (1, 2, 3, 4, ect…)

Each customer has objects in all three schemas, with the Global and Permissions schema object names, containing the customers ID number followed by an _

Example;

[1].[Test]
[Global].[1_Test]
[Permissions].[1_Test]

So far, I have written the bellow script that does this, but only keeps one customers data, not multiple;

    DECLARE @TenantId NVARCHAR(10)
DECLARE @Commands NVARCHAR(MAX)
DECLARE @Statements TABLE
    (
        Commands NVARCHAR(500)
    )

SET @TenantId = '686'

INSERT INTO @Statements
    EXECUTE  (

'SELECT ''DROP TABLE'' + '' '' + ''['' + s.[name] + '']'' + ''.'' + ''['' + t.[name] + '']'' AS ''Commands''

FROM sys.schemas s
    JOIN sys.tables t on s.schema_id = t.schema_id

WHERE   s.name <> '''+ @TenantId +''' 
AND         s.name <> ''dbo''
AND         t.name NOT LIKE '''+ @TenantId +'_%''
AND         t.name NOT IN (''DatabaseVersion'', ''PermissionTypes'')

ORDER BY s.name, t.name'    )

SELECT * FROM @Statements

--SET @Commands =(
--SELECT STRING_AGG (CAST([Commands] AS NVARCHAR(MAX)), ' ') FROM @Statements )

--SELECT (@Commands)

How can I convert this so that it takes multiple IDs into account, except for just one? I feel that I need to use a Cursor here, but not sure how that would be structured.

Thanks in advance.

Best Answer

I've cracked it! Thought I'd share in case anyone else was interested too. 

CREATE TABLE #Safe
    ( [Schema] NVARCHAR(50), [Table] NVARCHAR(100) )

INSERT INTO #Safe
SELECT SCHEMA_NAME(schema_id) AS [schema], [name]
    FROM sys.tables
        WHERE SCHEMA_NAME(schema_id) IN ('1', '2')
        OR [name] LIKE ('1_%')
        OR [name] LIKE ('2_%')
        OR [name] = 'DatabaseVersion'

DECLARE cur_delete CURSOR FOR
SELECT SCHEMA_NAME(schema_id) AS [schema], [name]
    FROM sys.tables
        WHERE SCHEMA_NAME(schema_id) NOT IN (SELECT [Schema] FROM #Safe)
        OR [name] NOT IN (SELECT [Table] FROM #Safe)

OPEN cur_delete

DECLARE @Schema NVARCHAR(50)
DECLARE @Table sysname
DECLARE @SQL NVARCHAR(MAX)

FETCH NEXT FROM cur_delete
INTO @Schema, @Table;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP TABLE [' + @Schema + '].[' + @Table + '];';
    PRINT @SQL
    -- EXEC sp_executesql @SQL;

FETCH NEXT FROM cur_delete INTO @Schema, @Table
END

CLOSE cur_delete
deallocate cur_delete

DROP TABLE #Safe

Found this post: Drop all tables from database command apart from named few

It included a Cursor that, with some modification, I was able to get working for my needs.

Related Question