SQL Server Error Messages - Msg 3729

Error Message

Server: Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE '<Table Name>' because it is being referenced by object '<Object Name>'.

Causes

The SCHEMABINDING option when creating a view binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding and therefore no dependencies, otherwise this error message will be raised.

To illustrate, here’s a script that will show how this error message can be encountered:

CREATE TABLE [dbo].[Customer] (
    [CustomerID]          INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [FirstName]           VARCHAR(50) NOT NULL,
    [LastName]            VARCHAR(50) NOT NULL,
    [Address]             VARCHAR(100) NOT NULL,
    [City]                VARCHAR(50) NOT NULL,
    [State]               CHAR(2) NOT NULL,
    [ZIP]                 VARCHAR(10) NOT NULL
)
GO

CREATE VIEW [dbo].[Customer_NY]
WITH SCHEMABINDING
AS
SELECT [CustomerID], [FirstName], [LastName], [Address], [City], [State], [ZIP]
FROM [dbo].[Customer]
WHERE [State] = 'NY'
GO

As can be seen from the CREATE VIEW statement, the SCHEMABINDING option is included in the view creation. Issuing the following DROP TABLE statement will generate this error message:

DROP TABLE [dbo].[Customer]
GO

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

Solution / Work Around

To drop a table that participates in a view created with the SCHEMABINDING clause, the view needs to be dropped first. There’s no use for the view anymore since the underlying table used by the view is to be dropped already.

To determine other objects that use the table to be dropped, as there may be more than one, the [dbo].[sp_depends] system stored procedure can be used. The [dbo].[sp_depends] system stored procedure displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure.

EXECUTE sp_depends '[dbo].[Customer]'

name              type
----------------- ----
dbo.Customer_NY   view

According to Books Online, the [dbo].[sp_depends] system stored procedure will be removed in a future version of Microsoft SQL Server and its use needs to be avoided in new development work and plans be made by developers using this system stored procedure to modify their applications. The suggested replacement for this feature is the sys.dm_sql_referencing_entities and the sys.dm_sql_referenced_entities.

In this particular case, the sys.dm_sql_referencing_entities dynamic management function will be used as the object that depend on the table being dropped needs to be identified. The sys.dm_sql_referencing_entities returns one row for each entity in the current database that references another user-defined entity by name. A dependency between two entities is created when one entity, called the referenced entity, appears by name in a persisted SQL expression of another entity, called the referencing entity.

SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities ( 'dbo.Customer', 'object' )

referencing_schema_name  referencing_entity_name
------------------------ ------------------------
dbo                      Customer_NY

Once all objects that depend on the table being dropped has been identified, in this case just the [dbo].[Customer_NY] view, those objects need to be dropped before the [dbo].[Customer] table can be dropped.

DROP VIEW [dbo].[Customer_NY]
GO

DROP TABLE [dbo].[Customer]
GO