Test and drop a SQL Server view if it exists in the database

How to test and then drop a SQL Server view, if it exists in the database?

Here is how to test the existence and drop a SQL Server view with a T-SQL script. Use this script as an example to test the existence of the view before deleting it. The goal is to not generate an error during the execution of a deployment script for example. And thus, not to execute a script on a object not in the database, or renamed for example.

1. Drop a SQL Server view if it exists with all versions

Indeed, deleting a non-existent view returns this error in SQL Server Management Studio (SSMS).

Cannot drop the view ‘dbo.V_SALES’, because it does not exist or you do not have permission.

Or the same error message in French:

Msg 3701: The view does not exist or you do not have permission.

Here is the solution, use the SQL Server IF EXISTS function to test the existence of the view before deleting it, as in this example.

IF EXISTS ( 
  SELECT 1 
  FROM sys.objects 
  WHERE object_id = object_id(N'[dbo].[V_SALES]') 
  AND type in (N'V') 
) 
BEGIN DROP VIEW [dbo].[V_SALES]; 
END;

2. Drop a view with the MS SQL version 2016 and older

Shorter script with only one line of code, the DROP VIEW IF EXISTS function, which is available since the SQL Server 2016 version.

IF EXISTS DROP VIEW [dbo];[V_SALES];

3. Drop all the views from a MS SQL database

Finally, for this third part on deleting views, here is how to create a dynamic script with a loop to filter and delete all views with a particular prefix or suffix. This multi-part code automates these steps:

1. Create the variables
2. Instantiate the variables
3. Create a slider to select the name of the views with a filter on the name
4. Browse the slider
5. For each row in the cursor, build and display a query to delete the view
6. Close and delete the cursor reference.

DECLARE
  @View_Name AS NVARCHAR(50),
  @Drop_SQL_Query AS NVARCHAR(100),
  @Drop_SQL_Query_Full AS NVARCHAR(200);

SET @Drop_SQL_Query = 'IF EXISTS DROP VIEW ';

DECLARE View_Name_Cursor CURSOR   FOR 
  SELECT [Name] 
  FROM SYS.VIEWS
  WHERE [Name] LIKE '%_SALES_%'
  ORDER BY [Name];

OPEN View_Name_Cursor;
FETCH NEXT FROM View_Name_Cursor into @View_Name;

WHILE @@FETCH_STATUS = 0  
BEGIN 
  SET @Drop_SQL_Query_Full = @Drop_SQL_Query + @View_Name;
  PRINT @Drop_SQL_Query_Full;
  --EXEC @Drop_SQL_Query_Full;
  FETCH NEXT FROM View_Name_Cursor into @View_Name;
END

CLOSE View_Name_Cursor;  
DEALLOCATE View_Name_Cursor; 

Indeed, this script allows you to drop all the views created in a database. It uses a filter, which can be easily modified to select views based on a suffix, a prefix, a filter as in the example above, or simply all views in the database.

This script does not actually drop the MS SQL views, but dynamically generates the commands to drop the views. To run it directly inside the T-SQL code, simply remove the comments on the following line.

EXEC sp_executesql @Drop_SQL_Query_Full;

These are Data Definition Language (DDL) queries. This more general page on views shows some examples of the main DDL commands for managing views with T-SQL scripts. To conclude, this tutorial explains how to test and drop a SQL Server view with versions before SQL Server 2016, and after in one line. Then presents a dynamic script to delete all views in a SQL database.

Manage SQL Server views with T-SQL code