MS-SQL - Drop All Tables, Stored Procedures,Function,Trigger and Views in SQL Server

Here is a script that I created that will drop all tables,stored procedures,functions and views in a SQL Server database.

Step 1
Execute following stored procedure on database.

CREATE PROCEDURE DBO.DropObject
(
    @ObjectName nvarchar(100),
    
    @Status nvarchar(100) OUTPUT
)
AS
    BEGIN
    
        DECLARE @ErrorMessage nvarchar(max) -- get the errormessage
        
        DECLARE @StartIndex bigint -- counter while loop
        SET @StartIndex=1
        
        DECLARE @CountElement bigint -- count the total number of procedures,tables etc
        DECLARE @_ObjectName nvarchar(100) -- Specify the Object name like sys.procedures,sys.tables etc
    
        DECLARE @Flag bit
        SET @Flag=0                 
                     
        
        DECLARE @ParmDefination nvarchar(max)
        DECLARE @ElementName nvarchar(max)
        DECLARE @TempQuery nvarchar(max)
        DECLARE @TempDropQuery nvarchar(max)
        
                     
        BEGIN TRY
        
            --Procedures 
        
            IF @ObjectName=LOWER('procedure') OR @ObjectName=UPPER('procedure') 
                BEGIN
                            -- get the count of total procedures    
                            SET @CountElement=(SELECT COUNT(name) FROM sys.procedures)
                            SET @_ObjectName='sys.procedures'
                            
                            SET @Flag=1 
                END 
                
            -- tables
            ELSE IF @ObjectName=LOWER('table') OR @ObjectName=UPPER('table')
                BEGIN
                        
                        -- get the count of total tables    
                        SET @CountElement=(SELECT COUNT(name) FROM sys.tables) 
                        SET @_ObjectName='sys.tables' 
                        
                        SET @Flag=1 
                END
                
            -- views
            ELSE IF @ObjectName=LOWER('view') OR @ObjectName=UPPER('view')
                BEGIN
                        
                        -- get the count of total views    
                        SET @CountElement=(SELECT COUNT(name) FROM sys.views)
                        SET @_ObjectName='sys.views'  
                        
                        SET @Flag=1 
                END   
                
            -- trigger         
            ELSE IF @ObjectName=LOWER('trigger') OR @ObjectName=UPPER('trigger')    
                BEGIN
                        
                        -- get the count of total triggers    
                        SET @CountElement=(SELECT COUNT(name) FROM sys.triggers)
                        SET @_ObjectName='sys.triggers'  
                        
                        SET @Flag=1 
                END 
                
            -- user defined function
            ELSE IF @ObjectName=LOWER('function') OR @ObjectName=UPPER('function')    
                BEGIN
                        
                    -- get the count of total functions    
                    SET @CountElement=(SELECT COUNT(name) FROM sys.objects WHERE type_desc LIKE '%FUNCTION%')
            
                        IF @CountElement>=1 
                            BEGIN
                                WHILE @StartIndex<=@CountElement   -- while loop
                                    BEGIN
                                        
                                        -- get the function name(element) from dynamic query
                                        SET @TempQuery='SELECT @ElementName=name FROM sys.objects WHERE type_desc LIKE ''%FUNCTION%'''  
                                        SET @ParmDefination=N'@ElementName nvarchar(max) OUTPUT'
                                        EXEC SP_EXECUTESQL @TempQuery,@ParmDefination,@ElementName=@ElementName OUTPUT 

                                        -- drop dynamic query                                
                                        SET @TempDropQuery='DROP '+@ObjectName+' '+@ElementName
                                        
                                        -- execute drop dynamic query
                                        BEGIN TRY
                                            EXEC(@TempDropQuery)
                                            SET @Status=CAST(@StartIndex as varchar)+' '+@ObjectName+'s '+'sucessfully droped'
                                        END TRY
                                        BEGIN CATCH
                                        END CATCH
                                
                                        SET @StartIndex=@StartIndex+1  -- while counter
                                        
                                    END  
                                                    
                            END
                        ELSE
                            BEGIN
                                SET @Status='No function on database' 
                            END 
                            
                                SET @Flag=0  
                END     
                        IF @Flag=1
                            
                            BEGIN
                                
                                IF @CountElement>=1 
                                    BEGIN
                                        
                                WHILE @StartIndex<=@CountElement -- while loop
                        
                                    BEGIN
                                                -- get the element name from dynamic query                
                                                SET @TempQuery='SELECT TOP 1 @ElementName=name FROM '+@_ObjectName+' ORDER BY name DESC' 
                                                SET @ParmDefination=N'@ElementName nvarchar(max) OUTPUT'
                                                EXEC SP_EXECUTESQL @TempQuery,@ParmDefination,@ElementName=@ElementName OUTPUT
                                        
                                        
                                            -- drop only procedures
                                            IF @ObjectName=LOWER('procedure') OR @ObjectName=UPPER('procedure') 
                                                BEGIN
                                                        --skip DropObject procedure
                                                        IF @ElementName<>'DropObject'
                                                            BEGIN
                                                                -- drop dynamic query
                                                                SET @TempDropQuery='DROP '+@ObjectName+' '+@ElementName
                                                                
                                                                -- execute drop dynamic query
                                                                BEGIN TRY
                                                                    EXEC(@TempDropQuery)
                                                                    SET @Status=CAST(@StartIndex as varchar)+' '+@ObjectName+'s '+'sucessfully droped'      
                                                                END TRY
                                                                BEGIN CATCH
                                                                END CATCH
                                                                
                                                            END 
                                                        
                                                END
                                            ELSE
                                                BEGIN
                                                    
                                                    -- Drop other object like trigger,views,tables
                                                    
                                                    -- drop dynamic query
                                                    SET @TempDropQuery='DROP '+@ObjectName+' '+@ElementName
                            
                                                    -- execute drop dynamic query
                                                                BEGIN TRY
                                                                    EXEC(@TempDropQuery)
                                                                    SET @Status=CAST(@StartIndex as varchar)+' '+@ObjectName+'s '+'sucessfully droped'      
                                                                END TRY
                                                                BEGIN CATCH
                                                                END CATCH    
                                                END
                                        
                                        SET @StartIndex=@StartIndex+1 
                                        
                                    END          
                                
                                END
                        ELSE
                            BEGIN
                                SET @Status='No '+@ObjectName+ ' on database' 
                            END 
                                        
                            END  
                
                
        END TRY
        
        BEGIN CATCH
            SET @ErrorMessage=ERROR_MESSAGE()
            RAISERROR(@ErrorMessage,16,1)  
        END CATCH
        
    END

Step 2
Call above stored procedure,it is look like this

-- table
DECLARE @Status_Table nvarchar(100)
EXEC dbo.DropObject 'table',@Status_Table OUTPUT
PRINT(@Status_Table)  

-- stored procedure
DECLARE @Status_Procedure nvarchar(100)
EXEC dbo.DropObject 'procedure',@Status_Procedure OUTPUT
PRINT(@Status_Procedure)  

-- trigger
DECLARE @Status_Trigger nvarchar(100)
EXEC dbo.DropObject 'trigger',@Status_Trigger OUTPUT 
PRINT(@Status_Trigger)

-- view
DECLARE @Status_View nvarchar(100)
EXEC dbo.DropObject 'view',@Status_View OUTPUT 
PRINT(@Status_View)

-- function
DECLARE @Status_Function nvarchar(100)
EXEC dbo.DropObject 'function',@Status_Function OUTPUT 
PRINT(@Status_Function)

Download
Download Script