How do I delete a view?

db.productView01.drop()

←  Modify a ViewSupported Operations for Views →

Creating custom views in Microsoft Dynamics 365 Sales creates time efficiencies for many users. But what do you do with custom views when they become outdated or no longer relevant? There is no need to let them sit in a view graveyard.

Follow the steps below to help keep your CRM clean and lean by deleting old, no longer used views.

We’re moving! View this article on the FORVIS website here.

Contact Us Today

To Learn More About Our Services

• Let’s Talk

Related Articles

ADDITIONAL POSTS

In this SQL Server tutorial, we will understand how to delete a view in SQL Server. Additionally, we will also cover the following related topics.

• How to delete a view in SQL Server
• How to delete a view in SQL Server using query
• How to delete a view in SQL Server Management Studio
• How to delete data from a view in SQL Server
• How to delete records from view in SQL Server
• How to delete view table in SQL Server
• How to delete all views in SQL Server
• How to delete multiple views in SQL Server
• How to drop and recreate a view in SQL Server
• How to drop a column from a view in SQL Server

Table of Contents

How to delete a view in SQL Server

Before understanding the methods to delete a view in SQL Server, we have to discuss a few things. First, we need to understand the required permissions to delete a view. Second, we need to understand the limitations of deleting a view.

In SQL Server, a user needs to have permission to delete a view.

• Either a user requires to have ALTER permission on the schema where the view exists.
• Alternatively, a user needs to have CONTROL permission on the object.

Whenever we delete a view in SQL Server, the view definition, as well as any information about it, gets removed from the system catalog. Additionally, all the permissions related to that view are also get removed.

Now that we know about the required permissions and limitations of deleting a view let’s, discuss the methods. And in SQL Server, there are two ways to delete a view, the first method is to write a Transact-SQL query, and the second method is by using SQL Server Management Studio.

Let’s discuss each method using an example in SQL Server.

How to delete a view in SQL Server using query

To delete a view in SQL Server using a query is quite simple. And for this task, we can use the following syntax.

USE databse_name;  
GO  
IF OBJECT_ID ('view_name', 'V') IS NOT NULL  
DROP VIEW view_name;  
GO

In the above syntax, first, we will specify the database where the view exists. Next, we are checking the existence of view using the “IF OBJECT_ID ” statement. In the end, we are using the DROP VIEW statement to delete the view from SQL Server.

Now, let’s use this syntax to delete a view from a SQL Server database.

USE [sqlserverguides] ;  
GO  
IF OBJECT_ID ('dbo.USA_CustomerView', 'V') IS NOT NULL  
DROP VIEW dbo.USA_CustomerView;  
GO 

In the above example, we are deleting a view from the sqlserverguides database. The name of the view is USA_CustomerView, and it returns data related to customers from the United States.

Alternative Way to delete a view in SQL Server using query

In SQL Server, there is also an alternative way to delete a view in SQL Server. First, let’s, undergo the following syntax.

DROP VIEW [IF EXISTS] view_name;

So, in the above syntax, IF EXISTS is an optional statement that we can use to check the existence of a view. Let’s undergo an example related to this syntax.

USE [sqlserverguides]
GO

DROP VIEW IF EXISTS [dbo].[USA_CustomerView]

In this example, again, we are doing the same task of deleting a view from the sqlserverguides database. And if everything goes well, then, the instance will return the following message.

How to delete a view in SQL Server Management Studio

Now, we can follow the following steps to delete a view in SQL Server Management Studio.

• First, run SQL Server Management Studio and connect to the required database instance.
• Next, from the Object Explorer, first, expand the Database instance and then expand the Databases directory.
• After this, expand the required database and then expand the Views directory.

• Under Views, right-click the required view and click on the Delete option. And it will open a delete object dialog box.

• In the end, click on the OK button to delete that view.

Also, check: SQL Server bulk insert from CSV file

How to delete records from view in SQL Server

In SQL Server, a VIEW is just like a virtual table that holds data from one or more than one table. However, we can also use a view to modify the data of an underlying table. But, we can perform such a task when there is only one base table in view.

Now, to understand this concept in a better way let’s, illustrate an example related to it. And for this, first, we will create a simple user-defined view in SQL Server.

USE [sqlserverguides]
GO

CREATE VIEW [dbo].[CustomerView]
( [Customer Name], [Customer City], [Customer Country] )
AS
SELECT customer_name, city, country 
FROM Customers
GO

In the above example, we have created a simple view which fetches data from the Customers table. And if we query this view, it will return the records from the Customers table.

Now, from the output, we can notice that the view returns results of Customers from multiple countries like Canada and the United States. Next, let’s understand how to delete some records using a view. And for this task, we will use the following query.

USE [sqlserverguides]
GO

DELETE FROM [dbo].[CustomerView]
WHERE [Customer Country] = 'United States'

In this example, we are using the DELETE statement to delete records using a view where the country is the United States. And this query will delete the records from both the Customers table and view.

Read: SQL Server function return table

How to delete multiple views in SQL Server

In SQL Server, deleting multiple objects from the database is quite easy. And same is the case while deleting multiple views in SQL Server. Here is a simple syntax that we can follow to delete multiple views in SQL Server.

USE database_name
DROP VIEW [IF EXISTS] 
    view_name1, 
    view_name2,
    ...;

So, according to the given syntax, we just need to specify the multiple view names that we want to delete. Here is one example that shows how to delete multiple views.

USE [sqlserverguides]
DROP VIEW IF EXISTS
  dbo.CA_CustomerView,
  dbo.CustomerOrderView,
  dbo.USA_CustomerView

And once the views are deleted, SQL Server will return the “Command completed successfully” statement.

Read: Arithmetic operators in SQL Server

How to delete all views in SQL Server

In the previous section, we have discussed how to delete multiple views from a database. Next, we will see how to delete all the views together in SQL Server. Now, there is no direct way to execute this task. So, we will execute this task using a trick in SQL Server.

First, let’s understand the following SQL query in SQL Server.

USE [sqlserverguides]
GO

DECLARE @view_name VARCHAR(500) 
DECLARE DropCursor CURSOR FOR SELECT [name] From sys.views 
OPEN DropCursor 
FETCH NEXT FROM DropCursor INTO @view_name 
WHILE @@fetch_status = 0 
BEGIN
 EXEC('DROP VIEW ' + @view_name)
 FETCH NEXT FROM DropCursor INTO @view_name 
END
CLOSE DropCursor 
DEALLOCATE DropCursor 

In the above query, we are performing the following tasks to delete all the views from the sqlserverguides database.

• First, we have declared a variable with the name view_name that will store the name of a view from the database.
• Next, we have created a CURSOR with the name DropCursor and this cursor will run a SELECT statement to fetch view names from sys.views.
• After this, the cursor will move to each view name from the sys.views and fetches the name into the view_name variable.
• Then, we have specified the BEGIN-END block where the DROP command will be executed.
• In the end, we have closed and deallocated the cursor.

At last, we can also check the existence of the view using the following query.

USE [sqlserverguides]
GO

SELECT * FROM sys.views

Now, if everything went well then the query will return an empty resultset.

Read: SQL Operand data type real is invalid for modulo operator

How to drop a column from a view in SQL Server

In SQL Server, we can use a simple user-defined view to modify the data of an underlying table. For example, we can insert new rows using the INSERT statement, and we can also delete rows using the DELETE statement.

But, we cannot modify the definition of a table using a view. And similarly, we cannot use a view to DROP a column from a table. As this task is not possible and if we try, SQL Server will return an error.

But, as an alternative, we can use the ALTER statement to modify the definition of a view. And that time, we can remove the unwanted column from the view. But, still, that column will be available in the table.

Let’s understand this scenario using an example and first, we will create a view using the following query.

USE [sqlserverguides] ;  
GO  
IF OBJECT_ID ('dbo.USA_CustomerView', 'V') IS NOT NULL  
DROP VIEW dbo.USA_CustomerView;  
GO 

In the above query, we are creating a simple view that fetched customer data from the Customers table where the country is the United States. Here is the final result of this view.

Now, we will use the ALTER statement to modify the definition of this view. Moreover, we will remove the country code column from the view. Here is the query for this task in SQL Server.

USE [sqlserverguides] ;  
GO  
IF OBJECT_ID ('dbo.USA_CustomerView', 'V') IS NOT NULL  
DROP VIEW dbo.USA_CustomerView;  
GO 

Now, the country code column is removed from the view, and we will get the following result.

Read: Full-text search in SQL Server

How to drop and recreate a view in SQL Server

In SQL Server, deleting and recreating a view is a very easy task. For this, first, we need to use the DROP statement to delete a view. And then, we can again use the CREATE VIEW statement to create a new view with the same name.

Here is a standard syntax that we can use for this task in SQL Server.

USE [sqlserverguides] ;  
GO  
IF OBJECT_ID ('dbo.USA_CustomerView', 'V') IS NOT NULL  
DROP VIEW dbo.USA_CustomerView;  
GO 

In the above syntax, first, we have to specify the database where the view exists. Next, we are using the DROP VIEW statement with IF EXISTS. And this will delete the specified view based upon its existence. In the end, we are using CREATE VIEW statement to create a new view in the same database.

Now, let’s use this syntax to implement an example. And the query for the example is given below.

USE [sqlserverguides] ;  
GO  
IF OBJECT_ID ('dbo.USA_CustomerView', 'V') IS NOT NULL  
DROP VIEW dbo.USA_CustomerView;  
GO 

In the above example, we are deleting and recreating the USA_CustomerView in the sqlserverguides database.

You may also like to read the following articles on SQL Server.

• What is a stored procedure in sql server
• How to call a view in SQL Server
• How to see view definition in SQL Server
• Temp table in stored procedure in SQL Server
• How to execute stored procedure in SQL Server
• SQL Server stored procedure case statement
• SQL Server scheduled stored procedure
• Indexed views in SQL Server

So, in this tutorial, we have understood how to delete a view in SQL Server. Moreover, we have also covered the following related topics.

• How to delete a view in SQL Server
• How to delete a view in SQL Server using query
• How to delete a view in SQL Server Management Studio
• How to delete data from a view in SQL Server
• How to delete records from view in SQL Server
• How to delete view table in SQL Server
• How to delete all views in SQL Server
• How to delete multiple views in SQL Server
• How to drop and recreate a view in SQL Server
• How to drop a column from a view in SQL Server

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.

Which command will delete a view?

How are views created and deleted?

Can we delete from views?

What is correct syntax for deleting a view?