Post reply

SELECT * in a view

  • October 13, 2006 at 5:47 am

    #169985

    I have view which use SELECT * FROM Something;

     

    If fields are changed in the something - how do I cascade this down any views that depend on the something.

     

    ie If I add a field to "something"  - I want it to appear automaticaaly in the SELECT * FROM something.

     

    Opening one of the follow on views and then saving with no changes corrects the views.

     

    Is there some code to do this for me?

     

    Thanks

  • October 13, 2006 at 6:34 am

    #665697

    exec sp_refreshview 'dbo.DEMO'

    This will force a recompile of the view definition.

    But it doesn't say anywhere that it recompiles the other objects accessing the view.

    Anyone can confirm this behavior?

  • October 13, 2006 at 10:46 am

    #665796

    Here's something from BOL:

    -------------------

    If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

    ------------------

    So you need to refresh the view for the changes to take effect.

    Also, lets say you do a schema change on a table and if you are not sure what all views are referencing it here's something from BOL as well:

    ----------------------------

    Creating a script that updates all views that have dependencies on a changed object

    Assume that the table Person.Contact was changed in a way that would affect the definition of any views that are created on it. The following example creates a script that refreshes the metadata for all views that have a dependency on table Person.Contact.

    USE AdventureWorks;

    GO

    SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''

    FROM sys.objects so INNER JOIN sys.sql_dependencies sd

    ON so.object_id = sd.object_id

    WHERE type = 'V'

    AND sd.referenced_major_id = object_id('Person.Contact')

    -------------------

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • October 17, 2006 at 8:49 am

    #666256

    Thanks both of you for the solution.

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

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