SQL Server: Tips and Tricks - 2 (2017)

How to drop View from Multiple or All Databases in SQL Server

Scenario:

You are working as SQL Server DBA or SQL Server developer, you need to come up with scripts to drop the view from many databases in one of your SQL Server Instance.

Solution:

Here is the script that can help to drop the view from multiple databases. I have used the cursor to loop through the databases. You can always modify your select query to choose required databases on which you would like to run the script. You have to change the schema name and view name and in drop view statement, the name of view.

--Provide the DDL Statment that you would like to run to drop the View if exists in Database

--Change the schema and View name in script as per your requirement

DECLARE @DDL VARCHAR(MAX)

SET @DDL='IF (EXISTS (SELECT *

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = ''''dbo'''' -- change to your Schema

AND TABLE_NAME = ''''MyTestView'''' --Change to your View Name

and Table_Type=''''View''''))

BEGIN

Drop view dbo.MyTestview

END'

DECLARE @DatabaseName AS VARCHAR(500)

--DECLARE CURSOR

DECLARE CUR CURSOR

FOR

--Choose the DBs on which you would like to run the script

SELECT NAME

FROM sys.databases

WHERE database_id > 4

--OPEN CURSOR

OPEN CUR

--NEXT NEXT RECORD

FETCH NEXT

FROM CUR

INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @DBName AS NVARCHAR(500);

SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

--USE Dynamic SQL To Change DB name and run DDL statement to drop View

EXEC (

N'USE ' + @DBName + N'; EXEC('''+@DDL+''');'

);

FETCH NEXT

FROM CUR

INTO @DatabaseName

END

CLOSE CUR

DEALLOCATE CUR