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

Scenario: How to drop View from multiple or all SQL Server Databases

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. 

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

No comments:

Post a Comment