SQL: Review Schema Owned Objects - Then Drop

SQL Copilot

-- Script: List and drop all objects owned by a schema, then drop the schema.
-- Usage: set @SchemaName and review the printed statements. To actually run them set @Execute = 1 (confirm first).
-- Always test on a non-production copy first.

SET NOCOUNT ON;
GO

DECLARE @SchemaName SYSNAME = N'TMT';  -- <--- change to target schema
DECLARE @Execute BIT = 1;                     -- 0 = only list, 1 = execute (confirm before setting to 1)

DECLARE @drops TABLE (
    seq INT,
    objtype NVARCHAR(60),
    stmt NVARCHAR(MAX)
);

-- 1) Drop views, procs, functions, synonyms, sequences, triggers (objects inside the schema)
INSERT INTO @drops (seq, objtype, stmt)
SELECT 10, 'VIEW',
    'IF OBJECT_ID(N''' + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ''',''V'') IS NOT NULL DROP VIEW ' + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ';'
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE s.name = @SchemaName;

INSERT INTO @drops (seq, objtype, stmt)
SELECT 10, 'PROCEDURE',
    'IF OBJECT_ID(N''' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ''',''P'') IS NOT NULL DROP PROCEDURE ' + QUOTENAME(s.name) + '.' + QUOTENAME(p.name) + ';'
FROM sys.procedures p
JOIN sys.schemas s ON p.schema_id = s.schema_id
WHERE s.name = @SchemaName;

INSERT INTO @drops (seq, objtype, stmt)
SELECT 10, 'FUNCTION',
    'IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N''' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ''') AND type IN (''FN'',''IF'',''TF'')) DROP FUNCTION ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';'
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = @SchemaName AND o.type IN ('FN','IF','TF');

INSERT INTO @drops (seq, objtype, stmt)
SELECT 10, 'SYNONYM',
    'IF OBJECT_ID(N''' + QUOTENAME(s.name) + '.' + QUOTENAME(syn.name) + ''',''SN'') IS NOT NULL DROP SYNONYM ' + QUOTENAME(s.name) + '.' + QUOTENAME(syn.name) + ';'
FROM sys.synonyms syn
JOIN sys.schemas s ON syn.schema_id = s.schema_id
WHERE s.name = @SchemaName;

INSERT INTO @drops (seq, objtype, stmt)
SELECT 10, 'SEQUENCE',
    'IF OBJECT_ID(N''' + QUOTENAME(s.name) + '.' + QUOTENAME(seq.name) + ''',''SQ'') IS NOT NULL DROP SEQUENCE ' + QUOTENAME(s.name) + '.' + QUOTENAME(seq.name) + ';'
FROM sys.sequences seq
JOIN sys.schemas s ON seq.schema_id = s.schema_id
WHERE s.name = @SchemaName;

-- Table triggers (DROP TRIGGER ... ON [schema].[table]) — fixed to use sys.objects for trigger metadata
INSERT INTO @drops (seq, objtype, stmt)
SELECT 10, 'TRIGGER',
    'IF EXISTS(SELECT 1 FROM sys.triggers WHERE object_id = ' + CONVERT(NVARCHAR(20), tr.object_id) + ')
     DROP TRIGGER ' + QUOTENAME(ts.name) + '.' + QUOTENAME(tobj.name) + ' ON ' + QUOTENAME(ps.name) + '.' + QUOTENAME(po.name) + ';'
FROM sys.triggers tr
JOIN sys.objects tobj ON tr.object_id = tobj.object_id        -- trigger object (name, schema_id)
JOIN sys.schemas ts ON tobj.schema_id = ts.schema_id         -- trigger schema
JOIN sys.objects po ON tr.parent_id = po.object_id           -- parent table object
JOIN sys.schemas ps ON po.schema_id = ps.schema_id           -- parent table schema
WHERE ps.name = @SchemaName;

-- 2) Foreign keys: drop constraints where parent or referenced table is in target schema
INSERT INTO @drops (seq, objtype, stmt)
SELECT 20, 'FOREIGN KEY',
    'ALTER TABLE ' + QUOTENAME(ps.name) + '.' + QUOTENAME(po.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys fk
JOIN sys.objects po ON fk.parent_object_id = po.object_id
JOIN sys.schemas ps ON po.schema_id = ps.schema_id
JOIN sys.objects ro ON fk.referenced_object_id = ro.object_id
JOIN sys.schemas rs ON ro.schema_id = rs.schema_id
WHERE ps.name = @SchemaName OR rs.name = @SchemaName;

-- 3) Tables (after FKs dropped)
INSERT INTO @drops (seq, objtype, stmt)
SELECT 30, 'TABLE',
    'IF OBJECT_ID(N''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''',''U'') IS NOT NULL DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = @SchemaName;

-- 4) User-defined types in the schema (drop after tables)
INSERT INTO @drops (seq, objtype, stmt)
SELECT 40, 'TYPE',
    'IF EXISTS(SELECT 1 FROM sys.types WHERE is_user_defined = 1 AND name = N''' + t.name + ''' AND schema_id = ' + CONVERT(NVARCHAR(10), t.schema_id) + ')
     DROP TYPE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.types t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_user_defined = 1 AND s.name = @SchemaName;

-- 5) Finally, drop the schema itself
INSERT INTO @drops (seq, objtype, stmt)
VALUES (100, 'SCHEMA', 'IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = N''' + @SchemaName + ''') DROP SCHEMA ' + QUOTENAME(@SchemaName) + ';');

-- Show the planned DROP statements (ordered)
SELECT seq, objtype, stmt
FROM @drops
ORDER BY seq, objtype;

-- If Execute flag set, run statements one by one (with TRY/CATCH)
IF @Execute = 1
BEGIN
    PRINT '*** EXECUTION STARTED: dropping objects for schema ' + @SchemaName + ' ***';
    DECLARE @stmt NVARCHAR(MAX);

    DECLARE cur CURSOR FAST_FORWARD FOR
       SELECT stmt FROM @drops ORDER BY seq, objtype;

    OPEN cur;
    FETCH NEXT FROM cur INTO @stmt;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Executing: ' + @stmt;
        BEGIN TRY
            EXEC sp_executesql @stmt;
        END TRY
        BEGIN CATCH
            PRINT 'ERROR: ' + ERROR_MESSAGE();
            CLOSE cur;
            DEALLOCATE cur;
            THROW;  -- re-raise so caller sees failure
        END CATCH;

        FETCH NEXT FROM cur INTO @stmt;
    END

    CLOSE cur;
    DEALLOCATE cur;
    PRINT '*** EXECUTION FINISHED ***';
END
ELSE
BEGIN
    PRINT 'Execution skipped. Set @Execute = 1 only after reviewing the statements above and confirming.';
END
GO