Useful little code snippet that removes all views from your database - we are getting the list of view from the database using this: SELECT * FROM INFORMATION_SCHEMA.VIEWS
Enjoy, Jon
DB Gurus
11 March 2016
------------------------------------------------------------------------------------
-- BACKUP YOUR DATABASE BEFORE YOU RUN THIS --
-- Create a temp table variable to store all the view names we are going to DROP
DECLARE @t TABLE
(
FullViewName varchar(8000),
id int identity(1,1)
)
-- Add them
INSERT INTO @t
SELECT TABLE_SCHEMA + '.[' + TABLE_NAME + ']' FROM INFORMATION_SCHEMA.VIEWS -- you can add drop criteria here WHERE...
-- List those that are going to be dropped
SELECT * FROM @t
DECLARE @sSQL varchar(8000)
DECLARE @counter int
SET @counter = 1
-- Build and run the SQL statements to dropd the
WHILE EXISTS(SELECT * FROM @t WHERE id >= @counter)
BEGIN
SELECT @sSQL = 'DROP VIEW ' + FullViewName FROM @t WHERE id = @counter
PRINT @sSQL
EXEC (@sSQL)
SET @counter = @counter + 1
END
