Dropping all views, stored procedure and functions

Dropping all the views

1. Get all the views

select name from sys.objects where type='v' order by name

2. Drop all the views

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('V')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP VIEW ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

Dropping all the stored procedures

1. Display all the stored procedure.

Select name from sys.objects where type='p' order by name

2. Drop all the stored procedure

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('P')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP PROCEDURE ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

Dropping all the functions

1. Display all the existing functions

 select name from sys.objects where type='TF' or type='IF' or type='FN' order by name

2. Drop all the existing functions

DECLARE @viewName varchar(500)
DECLARE cur CURSOR
      FOR SELECT [name] FROM sys.objects WHERE type in ('TF','IF','FN')
      OPEN cur

      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP Function ' + @viewName)
Select @viewName
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur