May 28, 2015 at 10:14 am
It was just brought to my attention that when a field size in a table is altered, the field size in a view is not. I always thought the view pulled this from the table.
Is there anyway to fix this without dropping my view and recreating it?
Example:
Create table MYTABLE
(field1 varchar(30),
field2 varchar(10))
Create VIEW VW__MYTABLE as
select field1, field2 from MYTABLE
sp_help mytable
sp_help vw__mytable
alter table mytable
alter column field1 varchar(50)
sp_help mytable -- field length changed to 50 as expected
sp_help vw__mytable -- field length remains at 30, why?
May 28, 2015 at 10:33 am
Have you tried with sp_refreshview?
May 28, 2015 at 1:54 pm
Thanks, doing this does cause my field size to display properly and so did scripting out my view and altering it (your suggestion was much faster though!).
However, I've modified tables without modifying views for years and can't believe I'm just now noticing this. Has it always been this way? The length of a field in views never change when the length of a field changes in a table.
May 28, 2015 at 2:06 pm
AFAIR, it has always been like this and it will give troubles as well when you use SELECT * and you change the order or number of columns in the table.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply