View field content update when "real" table fields change

[Silas]

Hi,

I use view to join difference table together for some function. However,
when the "real" table fields changed (e.g. add/delete/change field). The
view table still use the "old fields".

Therefore everytimes when I change the real table, I also needed open the
view table and save it by SQL enterprise manager manually for update the
view table field.

Can we use a SQL command or other method to update it directly?

Regards,
Silas

[David Portas]

So you are using SELECT * in your views rather than list the column
names? That's not a good idea. if you forget to update the view the
results can be unreliable when you drop and then add a column. Best
practice is to list the columns individually.

Do you always make schema changes directly through Enterprise Manager?
As you are discovering, that's hard work. Use scripts to make any
schema changes. You have more control that way, you also have a record
of what changed, you can more easily roll back changes if you need to
and you can reliably test out the change before you deploy it into
production. Using a script just requires an ALTER VIEW statement to
update your view. If you use Query Analyzer to do it then you don't
even need to type the column names yourself - drag the column list from
the Object Browser.

--
David Portas
SQL Server MVP
--

[markc600]

Check out sp_refreshview in BOL

[Erland Sommarskog]

David Portas (RE****************************@acm.org) writes:

Do you always make schema changes directly through Enterprise Manager?
As you are discovering, that's hard work.

Add to that the Modify Table function has some really horrible bugs, and
there are several problems with the scripts they generate.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Silas]

Thanks and it works.

<ma******@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Check out sp_refreshview in BOL