Thursday, August 23, 2012

Why can't I drop this table?




Cannot DROP TABLE 'dbo.PleaseDropMe' because it is being referenced by object '_dta_mv_25'.
Looking at the _dta_ prefix, it looked like a view created from database engine tuning advisor. The view was created with SCHEMABINDING . When a view is created with SCHEMABINDING specified, the underlying tables can not be modified in a way that would break the view. Explanation here. So in this case I first had to drop the view and then the table

 This is a nice feature if you would like to restrict some of your important tables from being 'accidentally' dropped. Lets test it:

USE BINYOGA
GO
IF OBJECT_ID('dbo.DropTest', 'U') IS NOT NULL
DROP TABLE dbo.DropTest;

CREATE TABLE dbo.DropTest (
 c1 int primary key,
 c2 char(2)
)
GO

-- Lets create a view

CREATE VIEW DropTestView WITH SCHEMABINDING
AS
SELECT c1, c2 FROM dbo.DropTest
GO

--Lets try and drop the table

DROP TABLE dbo.DropTest
GO

You'll see the error

Msg 3729, Level 16, State 1, Line 1 Cannot DROP TABLE 'dbo.DropTest' because it is being referenced by object 'DropTestView'.