Changing the size of a column referenced by a schema-bound view in SQL Server

Hi, 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.

Address_e is a view created by the previous DBA.

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

Thank you


more ▼

asked Dec 06, 2017 at 06:19 AM in Default

avatar image

11 1 3

Thank you anthony

Dec 06, 2017 at 09:31 AM kosmik5
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Script out the Address_e view as drop and create, drop the view, modify the column, recreate the view.

As the view is schemabound, the only way to modify the underlying schema is to remove the views dependency on binding.

more ▼

answered Dec 06, 2017 at 09:18 AM

avatar image

3.1k 1 4 6

@kosmik5 Because the view needs to be dropped and created anew, please make sure that you script out the explicit grants to it (if any). After the view is dropped, Addr1 column is altered, and the view is created, apply the explicit grants. This needs to be done in order to avoid a situation when some login which was able to select data from the view is now greeted with permission denied error.

Dec 06, 2017 at 01:41 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 06, 2017 at 06:19 AM

Seen: 40 times

Last Updated: Dec 06, 2017 at 01:41 PM

Copyright 2018 Redgate Software. Privacy Policy