Delete all views from Sql Server

Question

By using this statement in SQL Server:

EXEC sp_msforeachtable 'DROP TABLE ?'

I know it's possible to delete all tables at once.

Is there a similar statement for views? I tried this hoping to be lucky: EXEC sp_msforeachview 'DROP VIEW ?' but it doesn't work!

user_5283663 · Answer

Since no one of the scripts I tried from the answers worked correctly in the case of multiple schemas I am including a working one.

--DBNAME, PUT YOU OWN ONE use SIPE_ISU



DECLARE @viewName varchar(500)
DECLARE cur CURSOR FOR SELECT sk.name + '.'+so.name FROM sys.objects so inner join sys.schemas sk on sk.schema_id = so.schema_id WHERE type = 'v'
OPEN cur
      FETCH NEXT FROM cur INTO @viewName
      WHILE @@fetch_status = 0
      BEGIN
            EXEC('DROP VIEW ' + @viewName)
            FETCH NEXT FROM cur INTO @viewName
      END
      CLOSE cur
      DEALLOCATE cur

Delete all views from Sql Server

7 Answers