Sam

Posted on Oct 6 Originally published at woodfireerd.com

ALTER COLUMN failed because one or more objects access this column

#sql #database #dotnet #webdev

You change a column's type, and SQL Server refuses:

ALTER TABLE dbo.Product ALTER COLUMN Code nvarchar(20) NOT NULL;

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

The useful part is the 5074s above the 4922, one per thing in the way. Read those and you have your list. The trouble is that a deployment tool often shows only the last message, and 4922 on its own names nothing at all.

What counts as being in the way

An index that has the column as a key or an included column
A default constraint on the column
A check constraint that mentions the column, even one on a different column
A view or function created WITH SCHEMABINDING
A computed column whose expression uses it
A statistics object created by hand on the column

A foreign key is not on that list, and neither is a primary key on another column. Both can still block the change for their own reasons, but they are not what 4922 is about.

Not every change is blocked

Making a string longer is not a type change, and an index does not stop it:

CREATE TABLE dbo.Widen (Id int NOT NULL PRIMARY KEY, Code varchar(10) NOT NULL);
CREATE INDEX IX_Widen_Code ON dbo.Widen (Code);

ALTER TABLE dbo.Widen ALTER COLUMN Code varchar(50) NOT NULL;

That succeeds, index and all, and the index is rebuilt for you. varchar(10) to varchar(20), int to bigint, decimal(10,2) to decimal(12,2) — widening within the same type is allowed.

A schema-bound view is the exception that catches people out. Add one and even the widening above is refused, because WITH SCHEMABINDING is a promise that the column will not change shape underneath it:

Msg 5074, Level 16, State 1, Line 1
The object 'ProductCode' is dependent on column 'Code'.

So before assuming you are in for the whole drop-and-recreate dance, try the statement. If the change is a widening and nothing is schema-bound, you are already done.

Listing what holds the column

Rather than reading error messages one at a time, ask the database. This covers the three that account for most of them — indexes, default constraints, and anything schema-bound:

DECLARE @table sysname = 'dbo.Product';
DECLARE @column sysname = 'Code';

SELECT  i.name AS DependentObject, 'index' AS Kind
FROM    sys.indexes AS i
JOIN    sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN    sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE   i.object_id = OBJECT_ID(@table) AND c.name = @column

UNION ALL
SELECT  d.name, 'default constraint'
FROM    sys.default_constraints AS d
JOIN    sys.columns AS c ON c.object_id = d.parent_object_id AND c.column_id = d.parent_column_id
WHERE   d.parent_object_id = OBJECT_ID(@table) AND c.name = @column

UNION ALL
SELECT  OBJECT_NAME(sed.referencing_id), 'view or routine'
FROM    sys.sql_expression_dependencies AS sed
WHERE   sed.referenced_id = OBJECT_ID(@table)
  AND   sed.referenced_minor_id = COLUMNPROPERTY(OBJECT_ID(@table), @column, 'ColumnId');

DependentObject   Kind
----------------  ------------------
IX_Product_Code   index
DF_Product_Code   default constraint
ProductCode       view or routine

Check constraints are worth a separate look, because one can mention a column it is not attached to:

SELECT name, definition
FROM   sys.check_constraints
WHERE  parent_object_id = OBJECT_ID('dbo.Product')
  AND  definition LIKE '%Code%';

Clearing it, in order

Drop in this order, change the column, then rebuild in reverse:

DROP VIEW dbo.ProductCode;                            -- schema-bound things first
DROP INDEX IX_Product_Code ON dbo.Product;
ALTER TABLE dbo.Product DROP CONSTRAINT DF_Product_Code;

ALTER TABLE dbo.Product ALTER COLUMN Code nvarchar(20) NOT NULL;

ALTER TABLE dbo.Product ADD CONSTRAINT DF_Product_Code DEFAULT (N'NEW') FOR Code;
CREATE INDEX IX_Product_Code ON dbo.Product (Code);
GO
CREATE VIEW dbo.ProductCode WITH SCHEMABINDING AS SELECT ProductId, Code FROM dbo.Product;

Three things go wrong here more often than the change itself.

The default comes back subtly different. The column above became nvarchar, so the default is written N'NEW'. Recreate it as 'NEW' and every row inserted from then on takes an implicit conversion.

The index comes back without its options. Script the original before dropping it — the fill factor, the included columns, whether it is filtered, the filegroup. CREATE INDEX with none of that is a different index wearing the same name, and nobody notices until a query plan changes.

NOT NULL is not carried over. ALTER COLUMN takes the whole definition, so leaving the nullability off makes the column nullable, quietly:

ALTER TABLE dbo.Product ALTER COLUMN Code nvarchar(20);   -- now nullable

Before you start

The list of what holds a column is the hard part, and none of it is visible from the table definition: the view lives elsewhere, the check constraint may be on another column, and the index options only exist in the index.

That is what WoodFireERD reads for you — pick the table, see what points at it and what it points at, and get the change out as an ordered script with the drops, the alter and the rebuilds already in the right sequence.

Originally published at woodfireerd.com. Every statement in it was run against a real SQL Server before publishing — the error messages are copied from the output, not from memory.

DEV Community