Changing the size of a column in SQL Server

Question

Hi all,

I'm trying to change the size of a column in sql server using:

ALTER TABLE [dbo].[Address]
ALTER COLUMN [Addr1] [nvarchar](80) NULL

where the length of Addr1 was originally 40. It failed, raising this error:

The object 'Address_e' is dependent on column 'Addr1'.
ALTER TABLE ALTER COLUMN Addr1 failed because one or more objects access this column.

I've tried to read up on it and it seems that because some views are referencing this column and it seems that SQL Server is actually trying to drop the column that raised the error.

Is there any other way I can change the size of the column?

Answer 1

The views are probably created using the WITH SCHEMABINDING option and this means they are explicitly wired up to prevent such changes. Looks like the schemabinding worked and prevented you from breaking those views, lucky day, heh? Contact your database administrator and ask him to do the change, after it asserts the impact on the database.

From MSDN:

SCHEMABINDING

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.

Answer 2

See this link

Resize or Modify a MS SQL Server Table Column with Default Constraint using T-SQL Commands

the solution for such a SQL Server problem is going to be

Dropping or disabling the DEFAULT Constraint on the table column.

Modifying the table column data type and/or data size.

Re-creating or enabling the default constraint back on the sql table column.

Bye