Rotating Header Image

sp_refreshview How Did I Forget You?

So a developer asked me a question about how he added a column to a table and now the view that referenced the table wasn’t showing the column.  I asked the obvious question: “Did you add the column to the view?” to which he replied “The view is doing a SELECT *”

OK it may not be that bad… and it definitely not the reason for this post as I think enough people have wrote articles on why you shouldn’t use SELECT * …. so anyways I queried the view myself and saw the same thing that the developer saw.

So after a little “googling” I came across sp_refreshview.

sp_refreshview (Transact-SQL)

“Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.”

So I’m not sure if it’s because I don’t develop alot these days or if it’s because I name all my columns in my views directly but sp_refreshview slipped my mind so I thought I’d write a blog post about it just in case I’m not the only one.

Other ways to resolve this would be to do an ALTER VIEW and just keep the schema the same or to DROP and CREATE the view again (but you risk losing all permissions on the view so that’s just a bad idea).  If Microsoft gives you a “sp_refreshview” just for this reason… I say use it! :)

EDIT: Denis Gobo tweeted me with a great article he wrote as well on the subject at LessThanDot

Enjoy!!

Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.