Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10

    Red face Unanswered: Views with SELECT TableName.* aren't my friend

    I like views. Really I do. But trying to keep them dynamic is a pain in the butt. I have a view that, among other fields, selects TableName.*. Yes, I know selecting TableName.* is generally bad, but the columns in this table change from time to time and I don't want to update the view every time that happens.

    Anyway, the problem is that any time I add a column to the table, the view gets all freaky on my. It's almost like the view looks at the columns in the table as an array, and adding a column throws the array off by one spot. I've tried to use sp_recompile on the view, but that doesn't work. The only way that I've been able to get the view to properly recognize the new column is to physically open it, make a change, and resave it. But that sort of defeats the purpose of using SELECT TableName.* and having it dynamic. Does anyone know a way around this?

    Thanks!

    ~Mike

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Unfortunately, there is no really easy way to make a view dynamic. The view gets pre-compiled, and wants to stick with whatever form the table was in when it was first queried. This is why you are seeing the output get freaky.

    At best, you could make a stored procedure like this:

    create proc myProc
    as
    exec ('select * from tablename')
    go

    but in this case, you would have to make sure that any users running the stored proc have permissions on both the proc and the table.
    Hope this helps.

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Views with SELECT TableName.* aren't my friend

    If you use the stored procedure way you will have to check to see if the View exists or the code will error out.

    If(OBJECT_ID('View Name') IS NOT NULL)
    Drop VIEW View Name
    GO
    Create View View Name
    As
    Select *
    From Table Name



    Possible solution might be to package something similar to above as part of your maintenance plan and run it every night? That way if the table structure is changed the views will update correctly overnight and the users will not need access to the stored procedure (or maint plan in this case).

    Brent

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Just a question, why are you using views? Not that it is bad, but the under lying table changes its structure, why not SELECT data from the table instead.
    MCDBA

  5. #5
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10
    Originally posted by achorozy
    Just a question, why are you using views? Not that it is bad, but the under lying table changes its structure, why not SELECT data from the table instead.
    I am using the views because there are a ton of complex joins to many tables. One of those tables happens to be the one that I am selecting * from, but there are still a number of other tables. It is much easier to query from the view than to recreate these joins every time I need to get to the data.

  6. #6
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10

    Re: Views with SELECT TableName.* aren't my friend

    Originally posted by baolive
    If you use the stored procedure way you will have to check to see if the View exists or the code will error out.

    If(OBJECT_ID('View Name') IS NOT NULL)
    Drop VIEW View Name
    GO
    Create View View Name
    As
    Select *
    From Table Name



    Possible solution might be to package something similar to above as part of your maintenance plan and run it every night? That way if the table structure is changed the views will update correctly overnight and the users will not need access to the stored procedure (or maint plan in this case).

    Brent

    I appreciate the suggestion, but I don't think this will work. There are many tables and columns in this view, not just the table where I am selecting * from. And I have numerous queries that are using the view - some are stored procs, some are just queries. So it would it wouldn't really be a good idea to try to drop and create the view every time I query it.

  7. #7
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    Have you tried sp_refreshview on the view?

    Tim S

  8. #8
    Join Date
    Jul 2003
    Posts
    55

    ???

    Originally posted by TimS
    Have you tried sp_refreshview on the view?

    Tim S
    You guyyyyyssss,,,
    I posted the same question few weeks ago, did not get any reply! And now see how you are very intrested...
    Anyway, what Mr. TimS stated solved my problem, I discovered that SP and used it to refresh the querey.
    Hope that will work

  9. #9
    Join Date
    Jul 2003
    Location
    Tampa, FL
    Posts
    10

    Thumbs up

    Originally posted by TimS
    Have you tried sp_refreshview on the view?

    Tim S
    Well I'll be damned! That did it! Thanks, Tim!

  10. #10
    Join Date
    Jul 2003
    Posts
    2
    I get it,thx.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •