Post reply

SQL 200 view problem

  • February 5, 2007 at 6:16 am

    #63041

    Hi
    using SQL 2000
     
    i created a table (let - TA) and its view (sel * from TA) as VA
     
    after creating the view, i added one column to TA
    but the view is not showing it. the view must have to rebuild,
    bcz in sysColumns it doesn't shown the new col name.
     
    any easy method there have innorder to rebuild it.
     
    Thanks in advance

  • February 5, 2007 at 6:48 am

    #687731

    not an "automatic" way that I'm aware of;

    you can make it a habit  to recompile objects on a regular basis (exec sp_recompile object name) for all procs, views and functions,

    if you just want to recompile objects that refernece just one table you changed, this can get you started: note i limited the where statement to 'view', but you could leave it off for prcs and functions too.

    create table #ReferencedBy (ObjectName varchar(64),ObjectType varchar(20))

    Insert into #ReferencedBy(ObjectName,ObjectType)

    exec sp_depends YOURTABLE

    select 'exec sp_recompile ' + ObjectName from #ReferencedBy where ObjectType = 'view'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 5, 2007 at 7:10 am

    #687739

    The * in "select *" actually gets resolved to particular columns when the view is created. It is not then bound to the table. Any changes to the table will not change the view. You essentially need to drop and rebuild the view, though you can use alter instead.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • February 6, 2007 at 6:45 am

    #688010

    And yet another reason not to use SELECT * in a view.

  • February 6, 2007 at 9:28 am

    #688082

    sp_refreshview 'VIEWNAME'

     

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

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