If you’re getting an error that reads something like “Cannot DROP TABLE ‘dbo.t1’ because it is being referenced by object ‘v1’” in SQL Server, it looks like you’re trying to drop a table that’s referenced by a schema-bound object (such as a schema-bound view).

We can’t do things like drop tables if there’s a schema-bound dependency on it. A schema-bound dependency is created when a view or user-defined function is created by using the WITH SCHEMABINDING clause, or when a table references another object through a CHECK or DEFAULT constraint or in the definition of a computed column.

To fix this issue, identify all schema-bound dependencies, drop them, and then drop the table you were trying to drop. Alternatively, you may want to rethink dropping the table once you’ve reviewed its dependencies.

Example of Error

Suppose we create the following table and schema-bound view:

-- Create a table
CREATE TABLE dbo.t1
  (
    c1 int
  );

-- Create a schema-bound view that references that table
CREATE VIEW v1 WITH SCHEMABINDING AS 
SELECT c1 FROM dbo.t1;

And then we try to drop the table:

DROP TABLE IF EXISTS dbo.t1;

Output:

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

We got an error. That’s because the view is a schema-bound view. SQL Server won’t let us drop objects that schema-bound objects rely on. With schema-bound views, the base table/s cannot be modified in a way that would affect the view definition.

Solution 1

Views or tables that participate in a schema-bound view cannot be dropped (or altered) unless that view is dropped or changed so that it’s no longer schema-bound.

Therefore, the view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified.

Example of dropping the view before the table:

DROP VIEW dbo.v1;
DROP TABLE IF EXISTS dbo.t1;

Output:

Commands completed successfully.

This time the table (and view) were dropped as expected.

Solution 2

Here’s an example of modifying the view so that it’s no longer schema-bound:

ALTER VIEW v1 AS 
SELECT c1 FROM dbo.t1;

Now let’s drop the table:

DROP TABLE IF EXISTS dbo.t1;

Output:

Commands completed successfully.

Done.

Finding Dependencies

It’s good practice to check for dependencies before trying to drop tables and other objects.

One way to check for dependencies is with the sp_depends stored procedure:

EXEC sp_depends 'dbo.t1';

Output:

name    type
------  ----
dbo.v1  view

Note that I recreated the above table and view before I ran this.

Here’s another way to check for dependencies:

SELECT * FROM sys.dm_sql_referencing_entities('dbo.t1','OBJECT');

Output:

referencing_schema_name  referencing_entity_name  referencing_id  referencing_class  referencing_class_desc  is_caller_dependent
-----------------------  -----------------------  --------------  -----------------  ----------------------  -------------------
dbo                      v1                       1682105033      1                  OBJECT_OR_COLUMN        false