Post reply

Views with definition Select *

  • February 16, 2011 at 11:31 pm

    #231402

    I have a case where the view is defined as Select * from table1. But when a column is added to the table, the new column does not come up in the view.

    when I try to retrieve data from the view, I was not able to see the newly added column. Is there any way that newly added column to the base table gets auto-reflected in a view?

    Please suggest.

    Thanks,

    Deepthi

  • February 16, 2011 at 11:33 pm

    #1288048

    Most probably due to you scema binding is missing...plz check

  • February 16, 2011 at 11:40 pm

    #1288051

    I have verified that but with schema binding we need to explicitly mention the column names in Select statement. But here I have select * . And if I mention the column names then I may need to alter the view to add the new column.

    Thanks much!

    Deepthi

  • February 17, 2011 at 12:19 am

    #1288057

    As far as I know you will need to drop and re-crete the view. I would argue that having the view definition change just because an underlying table has an extra column would be a "bad thing" anyway.

    Can you imagine what would happen with nested views and views containing joins etc? If I added a column to a base table, and a view was based on it that joined to another table, if the new column I added was the same name as one in the other table all of a suddden my view breaks due to a name being ambiguous.

    So I think the right way out is name all the columns in the view - and if you want it changed then change the definition.

    Mike

  • February 17, 2011 at 12:58 am

    #1288065

    Thanks Mike! I just tried with sp_refreshview which worked great in this scenario.But as suggested by you, it is good to specify all the columns when defining a view.

    Thanks,

    Deepthi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply