Search Placementyogi.com
 

SQL Server Procedure to drop all views from a database

1. First create the below sql procedure in query analyzer. Just copy, paste and press F5.

CREATE PROC UserProc_DropViews
AS
BEGIN 
-- Variable to hold object name
DECLARE @name  VARCHAR(100)

-- Variable to hold object type
DECLARE @xtype VARCHAR(20)

-- Variable to hold sql string
DECLARE @sqlstring VARCHAR(1000)
 
DECLARE SPViews_cur CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS name, 'VIEW' AS xtype
FROM INFORMATION_SCHEMA.VIEWS
 
OPEN SPViews_cur
 
FETCH NEXT FROM SPViews_cur INTO @name, @xtype
 
WHILE @@FETCH_STATUS  = 0
  BEGIN
-- test object type if it is a view
   IF @xtype = 'VIEW'
      BEGIN
         SET @sqlstring = 'DROP VIEW ' + @name
         PRINT @sqlstring
         EXEC sp_executesql @sqlstring
        
         SET @sqlstring = ''
      END
 
-- Get the next record
    FETCH NEXT FROM SPViews_cur INTO @name, @xtype
  END
 
CLOSE SPViews_cur
DEALLOCATE SPViews_cur

END

2. Now execute the procedure using the below query to drop all views. Just copy the query and press F5.

EXEC UserProc_DropViews

Preview

Thanks for posting your comment! It will be visible soon!
Post Another Comment

8
+
5
=

Name:
Email :
Banner to LogicGuns.com

Hide Page Information