If you’re getting an error that reads something like “ALTER TABLE DROP COLUMN c2 failed because one or more objects access this column” in SQL Server, it looks like you’re trying to drop a column that’s accessed by another object.

This can happen when we try to drop a column that’s referenced by a schema-bound view. It can happen even when we only try to alter the columns too.

To fix this issue, either:

1. In the schema-bound object, remove the reference to the column before dropping that column from the base table.
2. Alter the schema-bound object so that it’s not schema-bound, and then drop the column.
3. Drop the schema-bound object, and then drop the column.

Example of Error

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

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

-- Create a schema-bound view
CREATE VIEW v1 WITH SCHEMABINDING AS 
    SELECT c1, c2 FROM dbo.t1;

And now let’s try to drop one of the columns in the base table:

ALTER TABLE dbo.t1
DROP COLUMN c2;

Output:

Msg 5074, Level 16, State 1, Line 1
The object 'v1' is dependent on column 'c2'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE DROP COLUMN c2 failed because one or more objects access this column.

We got two error messages. They’re both a result of me trying to drop a column that has dependencies.

We’ll get the same error even if we only try to alter the column:

ALTER TABLE dbo.t1
ALTER COLUMN c2 decimal(8,2);

Output:

Msg 5074, Level 16, State 1, Line 1
The object 'v1' is dependent on column 'c2'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.

Let’s go through each of the three solutions listed above.

Solution 1

In the schema-bound object, remove the reference to the column before dropping that column from the base table:

-- Remove the column reference from dependent object
ALTER VIEW v1 WITH SCHEMABINDING AS 
    SELECT c1 FROM dbo.t1;
GO
-- Drop the column from the base table
ALTER TABLE dbo.t1
DROP COLUMN c2;
GO

This is probably the best way to go if you still want the view, seeing as there’s no point in having a view that references a non-existent column.

Solution 2

Alter the schema-bound object so that it’s not schema-bound, and then drop the column:

-- Remove schemabinding from dependent object
ALTER VIEW v1 AS 
    SELECT c1, c2 FROM dbo.t1;
GO

-- Drop the column from the base table
ALTER TABLE dbo.t1
DROP COLUMN c2;
GO

While this option allows us to get past the error, it results in the view referencing a non-existent column, which is probably not what you want (unless you’ve got other changes planned).

Solution 3

Drop the schema-bound object, and then drop the column:

-- Drop the view
DROP VIEW dbo.v1;

-- Drop the column from the base table
ALTER TABLE dbo.t1
DROP COLUMN c2;

This would be an option if you no longer want the view. It may also be an option if you’re doing a complete overhaul and maybe you intend on recreating the view in the future with a different definition, but you’re not sure what that will look like, and your immediate priority is to drop/alter the column. Or something like that 😉