sqlsql-server-2008sp-msforeachtable

Delete all views from Sql Server

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!

Solution

  • Here you have, no cursor needed:

    DECLARE @sql VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

PRINT @sql;
EXEC(@sql);