How to drop a SQL Server view with a T-SQL query? The DROP VIEW command allows you to simply delete an existing view with a T-SQL script run from SQL Server Management Studio (SSMS).
The script below is an example of how to delete a view in a SQL database. That is, the view is deleted, but not the source tables to which it points. Note that the deletion is irreversible. It is therefore only the structure in T-SQL and not the data that is potentially lost.
Deleting an MS SQL view with a drop view script
Furthermore, it is true that views do not contain data, as they point to tables. Deleting a SQL Server view does not delete the underlying data, to dump all the data in a table, use a T-SQL script with the TRUNCATE TABLE command.
Indeed, a view is rather a structure with a selection query and potential filters to filter the displayed data.
This T-SQL script therefore uses the DROP VIEW command to remove it completely from the DBMS.
To use it, simply run it in an SSMS window connected to the target database.
DROP VIEW [dbo][V_SALES];
However, regular backups of your databases should be made to preserve the data and the valuable code that runs on it.
Dependencies between views
If a view calls another view, then it is always possible to use the intermediate view, without error.
To go further on the method and SQL code management, it’s a good practice to check the dependencies of the view before deleting it.
For example, let’s consider the first table Sales that is used by the view dbo.SalesByMonth. Let’s consider now a third object, a second view that display the Sales with a Year-To-Date vision: dbo.SalesByMonthYTD.
If you drop the first view, then the YTD one will not work anymore. So, it is a good practice to do an impact analysis to see the dependencies between objects in a database.
To execute the code below, first create the sales view whose code is available in this MS tutorial.
To learn more about SQL Server views, here is a tutorial English on partitioned views. A view with partitions allows you to display data from several source tables in a single query.
In practice each partition is a table.
Be the first to comment