October 30, 2009 at 8:35 am
This is a general question regarding views.
I have a simple view with data from table A and Table B and a join. When I change the data type of a column in table A from varchar to integer, then do sp_help on the view, the view still shows the column with the original data type. It appears as though the view does not detect the underlying column data type change.
When this happens I have been scripting the view as an ALTER statement and just re running it.
After doing this sp_help [ViewName] now displays the correct data type for the changed column.
Is there a better way to handle this type of change so column changes will be automatically reflected in the view ? Will creating my views with schema binding handle this better ?
Thanks for any information.
Bill, Charlotte NC
October 30, 2009 at 8:44 am
Hi
This depends on the ANSI-SQL standard which is complied here. It defines that a view's structure must not change until you change the view's DDL.
One way to do this is ALTER VIEW, just like you do. A more generic, and simpler solution might be the system procedure "sp_refreshview @viewname".
Greets
Flo
October 30, 2009 at 8:51 am
Thank you. I just did a simple test and sp_RefreshView was what I was looking for. I Must have had a brain cramp when I googled this topic before.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply