Changing a table will break a view?

The other day I updated a table to add a few columns using Enterprise
Manager. When I closed the table, it said it would update several other
database items as a result (very good, I thought).
However, subsequent to this change, even the most basic view is broken, e.g.
,
CREATE VIEW [v_table] AS select * from [table]
Recreating (or altering) the view clear up the problem. But, I was surprised
!
What is the canonical way to avoid views getting out of sync with tables? Is
there another way to modify table structure that will automatically recompil
e
other database objects?
David> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
The best practice is to specify a column list rather than '*'. You can
execute sp_refreshview to refresh view meta data after changes to underlying
tables.
Hope this helps.
Dan Guzman
SQL Server MVP
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
> e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
> surprised!
> What is the canonical way to avoid views getting out of sync with tables?
> Is
> there another way to modify table structure that will automatically
> recompile
> other database objects?
> David
>|||Create your views WITH SCHEMABINDING to avoid this problem. That will
"bind" the schema -- meaning that none of the underlying tables will be able
to change unless you drop the view.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in message
news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
> The other day I updated a table to add a few columns using Enterprise
> Manager. When I closed the table, it said it would update several other
> database items as a result (very good, I thought).
> However, subsequent to this change, even the most basic view is broken,
e.g.,
> CREATE VIEW [v_table] AS select * from [table]
> Recreating (or altering) the view clear up the problem. But, I was
surprised!
> What is the canonical way to avoid views getting out of sync with tables?
Is
> there another way to modify table structure that will automatically
recompile
> other database objects?
> David
>|||And you can automate this refresh using something like:
-- Cycle through all view in the current database and refresh their metadata
-- to take into account any changes to the underlying objects.
DECLARE @.ViewName sysname
DECLARE views_to_refresh CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
OPEN views_to_refresh
FETCH NEXT FROM views_to_refresh INTO @.ViewName
WHILE @.@.FETCH_STATUS = 0
BEGIN
print 'Refreshing ' + @.ViewName
exec sp_refreshview @.ViewName
FETCH NEXT FROM views_to_refresh INTO @.ViewName
END
CLOSE views_to_refresh
DEALLOCATE views_to_refresh
Thanks!
David
"Dan Guzman" wrote:

> The best practice is to specify a column list rather than '*'. You can
> execute sp_refreshview to refresh view meta data after changes to underlyi
ng
> tables.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "David W. Rogers" <DavidWRogers@.discussions.microsoft.com> wrote in messag
e
> news:BF4096CE-2601-45ED-AC52-AF89A528A7FC@.microsoft.com...
>
>sql