Eliminar todas las vistas del server Sql

Al usar esta statement en SQL Server:

EXEC sp_msforeachtable 'DROP TABLE ?' 

Sé que es posible eliminar todas las tablas a la vez.

¿Hay una statement similar para las vistas? Intenté esto con la esperanza de tener suerte: EXEC sp_msforeachview 'DROP VIEW?' ¡pero no funciona!

Aquí tienes, no se necesita cursor:

 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); 
 declare @SQL nvarchar(max) set @SQL = ( select 'drop view '+name+'; ' from sys.views for xml path('') ) exec (@SQL) 

Todas las respuestas no tienen en count las restricciones entre las vistas. Este script tendrá esto en count:

 SET @schemeName = 'dbo' SELECT @name = (SELECT TOP 1 o.[name] FROM sysobjects o inner join sys.views v ON o.id = v.object_id WHERE SCHEMA_NAME(v.schema_id) =@schemeName AND o.[type] = 'V' AND o.category = 0 AND o.[name] NOT IN ( SELECT referenced_entity_name FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referenced_schema_name = @schemeName ) ORDER BY [name]) WHILE @name IS NOT NULL BEGIN SELECT @SQL = 'DROP VIEW [' + @schemeName + '].[' + RTRIM(@name) +']' EXEC (@SQL) PRINT 'Dropped View: ' + @name SELECT @name = (SELECT TOP 1 o.[name] FROM sysobjects o inner join sys.views v ON o.id = v.object_id WHERE SCHEMA_NAME(v.schema_id) = @schemeName AND o.[type] = 'V' AND o.category = 0 AND o.[name] NOT IN ( SELECT referenced_entity_name FROM sys.sql_expression_dependencies AS sed INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id WHERE referenced_schema_name = @schemeName ) ORDER BY [name]) END GO 

Esto recorre todas las vistas y selecciona la vista TOP 1 que no está presente en las references systable.

Quería que un script descartara las vistas enlazadas al esquema en el order de dependencia correcto, y quería que se ejecutara en sql azure, donde sys.dm_sql_referencing_entities no está disponible. También quería poder ver el sql que se ejecuta antes de que realmente lo ejecutara, lo cual no se puede hacer con el script en la respuesta de @RicNet. Así que escribí esta consulta recursiva que usa las otras respuestas aquí como una base

 DECLARE @sql VARCHAR(MAX) = '' DECLARE @crlf VARCHAR(2) = CHAR(13) + CHAR(10); ;WITH allviews as ( --just combining schema and name SELECT object_id, '[' + SCHEMA_NAME(schema_id) + '].[' + name + ']' AS viewname FROM sys.views ), dependents AS ( SELECT referencing.viewname dependentname, referenced.viewname dependenton FROM sys.sql_expression_dependencies r INNER JOIN allviews referencing ON referencing.object_id = r.referencing_id INNER JOIN allviews referenced ON referenced.object_id = r.referenced_id ) , nodependents AS ( SELECT viewname name FROM allviews v LEFT JOIN dependents d ON d.dependentname = viewname WHERE d.dependentname IS NULL ) ,hierarchy AS ( --the hierarchy recurses the dependencies SELECT d.dependenton, d.dependentname, 1 tier FROM dependents d UNION ALL SELECT d.dependenton, d.dependentname, h.tier + 1 FROM dependents d INNER JOIN hierarchy h ON h.dependenton = d.dependentname --best thing I could think to stop the recursion was to --stop when we reached an item with no dependents WHERE h.dependenton NOT IN (SELECT name FROM nodependents) ), combined as ( --need to add item with no dependents back in SELECT 0 tier, name FROM nodependents UNION SELECT tier, dependentname FROM hierarchy ) SELECT @sql = @sql + 'DROP VIEW ' + name + ';' + @crlf FROM combined GROUP BY name --need to group because of multiple dependency paths ORDER BY MAX(tier) desc PRINT @sql; --commented out until I'm confident I want to run it --EXEC(@sql) 

Prueba este script

 DECLARE @viewName varchar(500) DECLARE cur CURSOR FOR SELECT [name] FROM sys.objects 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 

vea aqui para mas informacion

Pero, ¿y el esquema?
La secuencia de commands siguiente le ayudará si las vistas son parte del esquema

 DECLARE @sql VARCHAR(MAX)=''; SELECT @sql=@sql+'DROP VIEW '+name +';' FROM ( SELECT Name=[s].name + '.' + [v].name FROM sys.views [v] LEFT OUTER JOIN sys.schemas [s] ON ( [v].[schema_id]=[s].[schema_id] ) ) X EXEC(@sql) 
    Intereting Posts