DROP TABLE IF EXISTS fails
The DROP TABLE IF EXISTS does appear to be contextual, so it checks if the objects exists within sys.objects rather then the limited sys.tables, so the follow example would fail on the second run -
https://gist.github.com/matt40k/f1793ecc7e20738ebf5c0d7425e2a4ca
-- code start ---
DROP TABLE IF EXISTS dbo.ObjectFormerlyATable;
CREATE TABLE dbo.ObjectFormerlyATable
(
ID INT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_dbo_ObjectFormerlyATable PRIMARY KEY CLUSTERED (ID ASC)
);
INSERT INTO dbo.ObjectFormerlyATable DEFAULT VALUES;
SELECT ID FROM dbo.ObjectFormerlyATable;
DROP TABLE IF EXISTS dbo.ObjectFormerlyATable;
GO
CREATE OR ALTER VIEW dbo.ObjectFormerlyATable
AS
SELECT
ID=1
;
GO
SELECT ID FROM dbo.ObjectFormerlyATable;
--- code end ---
Other examples could be DACPAC failing to deploy as you used PreDeployment to IF EXISTS DROP TABLE, then create the view. The next deployment would result in a failure as it wasn't able to drop the table
Error outputted:
Cannot use DROP TABLE with 'dbo.ObjectFormerlyATable' because 'dbo.ObjectFormerlyATable' is a view. Use DROP VIEW.
Matt Smith
shared this idea
1 comment
-
Anonymous
commented
I have same error when running DROP TABLE IF EXISTS, and there is a view with the name of the table I'm trying to drop.
Strange, as view is not a table, why it fails?
It is still there in SQL Server 2019 with the latest CU.