patternsqlMinor
Why can you only drop a SQL Server view in the current database?
Viewed 0 times
whycantheyousqlviewdatabasedropcurrentserver
Problem
Just noticed this and was having a hard time coming up with a technical reason for it.
Tables can be dropped using three part names, but views are restricted to two part names.
What is the reasoning behind this?
Tables can be dropped using three part names, but views are restricted to two part names.
What is the reasoning behind this?
Solution
The documentation is skimpy on this, merely stating (and providing a syntax diagram) that only current database is relevant.
Removes one or more views from the current database.
Someone fluent in the debugger could determine if this is because of a code path that is only hit when dropping certain types of objects, or you could ask someone at Microsoft. Regardless of the reason, and whether or not you actually know the reason, you still need the same workarounds... you can do
Or:
Removes one or more views from the current database.
DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]Someone fluent in the debugger could determine if this is because of a code path that is only hit when dropping certain types of objects, or you could ask someone at Microsoft. Regardless of the reason, and whether or not you actually know the reason, you still need the same workarounds... you can do
USE your_database;
GO
DROP VIEW dbo.viewname;Or:
EXEC your_database.sys.sp_executesql N'DROP VIEW dbo.viewname;';Code Snippets
DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]USE your_database;
GO
DROP VIEW dbo.viewname;EXEC your_database.sys.sp_executesql N'DROP VIEW dbo.viewname;';Context
StackExchange Database Administrators Q#227400, answer score: 9
Revisions (0)
No revisions yet.