[MS-SQL] DB Schema 모두 제거하기

USE [DBName]
GO

DECLARE @SysObjectCount INT
SELECT @SysObjectCount = COUNT('') FROM SYSOBJECTS WITH(NOLOCK)
    
-- Drop Views
BEGIN
     DECLARE @IndexForView INT
     SET @IndexForView = 0

     WHILE ( @IndexForView < @SysObjectCount )
     BEGIN
        DECLARE @VIEW_NAME VARCHAR(MAX)
        DECLARE VIEW_LIST CURSOR FOR
            SELECT NAME FROM SYSOBJECTS O2 WHERE XTYPE='V'
        OPEN VIEW_LIST
        FETCH NEXT FROM VIEW_LIST INTO @VIEW_NAME 
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'DROPPING VIEW '+@VIEW_NAME
            EXEC ('DROP VIEW '+@VIEW_NAME) 
            FETCH NEXT FROM VIEW_LIST INTO @VIEW_NAME
        END 
        CLOSE VIEW_LIST
        DEALLOCATE VIEW_LIST
        SET @IndexForView = @IndexForView + 1
     END
END

-- Drop Procedures
BEGIN
     DECLARE @IndexForProcedure INT
     SET @IndexForProcedure = 0
    
     WHILE ( @IndexForProcedure < @SysObjectCount )
     BEGIN
        DECLARE @PROCEDURE_NAME VARCHAR(MAX)
        DECLARE PROCEDURE_LIST CURSOR FOR
            SELECT NAME FROM SYSOBJECTS O2 WHERE XTYPE='P'
        OPEN PROCEDURE_LIST
        FETCH NEXT FROM PROCEDURE_LIST INTO @PROCEDURE_NAME 
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'DROPPING PROCEDURE '+@PROCEDURE_NAME
            EXEC ('DROP PROCEDURE '+@PROCEDURE_NAME) 
            FETCH NEXT FROM PROCEDURE_LIST INTO @PROCEDURE_NAME
        END 
        CLOSE PROCEDURE_LIST
        DEALLOCATE PROCEDURE_LIST
        SET @IndexForProcedure = @IndexForProcedure + 1
     END
END

-- Drop User Defined Table
BEGIN
     DECLARE @IndexForUDTable INT
     SET @IndexForUDTable = 0
     WHILE ( @IndexForUDTable < @SysObjectCount )
     BEGIN
        DECLARE @UDTABLE_NAME VARCHAR(MAX)
        DECLARE UDTABLE_LIST CURSOR FOR
            SELECT NAME FROM SYS.TYPES WHERE SYSTEM_TYPE_ID = 243 AND IS_TABLE_TYPE = 1
        OPEN UDTABLE_LIST
        FETCH NEXT FROM UDTABLE_LIST INTO @UDTABLE_NAME 
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'DROPPING User Defined Table Type '+@UDTABLE_NAME
            EXEC ('DROP TYPE '+@UDTABLE_NAME) 
            FETCH NEXT FROM UDTABLE_LIST INTO @UDTABLE_NAME
        END 
        CLOSE UDTABLE_LIST
        DEALLOCATE UDTABLE_LIST
        SET @IndexForUDTable = @IndexForUDTable + 1
     END
END

-- Drop Tables
BEGIN
     DECLARE @IndexForTable INT
     SET @IndexForTable = 0
     WHILE ( @IndexForTable < @SysObjectCount )
     BEGIN
        DECLARE @TABLE_NAME VARCHAR(MAX)
        DECLARE TABLE_LIST CURSOR FOR
            SELECT NAME FROM SYSOBJECTS O2 WHERE XTYPE='U' AND
               NOT EXISTS (
                    SELECT * FROM SYSFOREIGNKEYS K
                       JOIN SYSCOLUMNS C1 ON (K.FKEYID = C1.ID AND C1.COLID=K.FKEY)
                       JOIN SYSCOLUMNS C2 ON (K.RKEYID = C2.ID AND C2.COLID=K.RKEY)
                    WHERE C2.ID = O2.ID AND C1.ID <> O2.ID
               )
        OPEN TABLE_LIST
        FETCH NEXT FROM TABLE_LIST INTO @TABLE_NAME 
        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'DROPPING TABLE '+@TABLE_NAME
            EXEC ('DROP TABLE '+@TABLE_NAME) 
            FETCH NEXT FROM TABLE_LIST INTO @TABLE_NAME
        END 
        CLOSE TABLE_LIST
        DEALLOCATE TABLE_LIST
        SET @IndexForTable = @IndexForTable + 1
     END
END