DROP all user created Stored Procedures, Functions, User Types, and Views

DROP_Procedures_Functions_User_Types_and_Views.sql

-- Stored Procedures
PRINT 'Dropping Stored Procedures'
DECLARE @sqlDropProcedure NVARCHAR(MAX) = N'';
SELECT  @sqlDropProcedure += 'DROP PROCEDURE [' + OBJECT_SCHEMA_NAME(object_id) + '].[' + name + ']; ' 
FROM sys.procedures 
WHERE is_ms_shipped = 0 
ORDER BY OBJECT_SCHEMA_NAME(object_id), name;

EXEC sp_executesql @sqlDropProcedure;

-- Functions
PRINT 'Dropping Functions'
DECLARE @sqlDropFunction NVARCHAR(MAX) = N'';
SELECT @sqlDropFunction += 'DROP FUNCTION [' + OBJECT_SCHEMA_NAME(object_id) + '].[' + OBJECT_NAME(object_id) + ']; ' 
FROM sys.objects 
WHERE is_ms_shipped = 0
    AND type IN ('FN', 'IF', 'TF') 
ORDER BY OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id);

EXEC sp_executesql @sqlDropFunction;

-- User Types
PRINT 'Dropping User Types'
DECLARE @sqlDropType NVARCHAR(MAX) = N'';
SELECT @sqlDropType += 'DROP TYPE [' + s.name + '].[' + t.name + ']; ' 
FROM sys.types t 
JOIN sys.schemas s 
    ON s.schema_id = t.schema_id 
WHERE t.is_user_defined = 1 
ORDER BY s.name, t.name

EXEC sp_executesql @sqlDropType;

-- Views
PRINT 'Dropping Views'
DECLARE @sqlDropView NVARCHAR(MAX) = N'';
SELECT @sqlDropView += 'DROP VIEW [' + s.name + '].[' + v.name + ']; ' 
FROM sys.views v 
JOIN sys.schemas s 
    ON s.schema_id = v.schema_id 
WHERE v.is_ms_shipped = 0
ORDER BY s.name, v.name;

EXEC sp_executesql @sqlDropView;