Renaming a View - Part 1

It's easy to create a view and realize after working with it some, or tweaking it's definition, that you've named it badly. Good names are worth having, so you just select the view in SSMS, click F2, edit the name, and you're done, brand new name. Right? Well, you can do that, but you're setting yourself up for a really unexpected problem later on. Today we'll show you why renaming in SSMS is a bad idea, and then in Part 2 we'll show you how it can really cause pain!

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Rob Firth on 2/14/2011

Great stuff. Has it been put forward as a bug?

Ed Svastits on 2/14/2011

Thanks for pointing out an idiosyncracy of SQL Server, Andy. One would think that SQL Server would have taken care of the metadata update when the name update occurred. Oh well, ...

Ludwig on 2/14/2011

It's properly the same with other db objects (Tables function etc.)as well. When renaming first drop the object and recreate it.right? Thx Ludwig

Don Weigend on 2/14/2011

Excellent tip! I'll bet that renaming a view in SSMS is more commonly done without anyone realizing that the definition is not changed as well.

Rich on 2/14/2011

I don't understand why MS doesn't update the view definition in the background. SSMS will prevent me from renaming the view to an already-defined view, so it obviously is checking view metadata under some circumstances.... Thanks for the heads up!

Radhika Mandani on 2/14/2011

I don't think the video was a complete one.

Rebecca Lewis on 2/14/2011

Good example.

Joanne Snyder on 2/14/2011

Thanks for sharing, as I never knew it behaved like this!

Stephen Oesterreicher on 2/14/2011

I did not know this and it is very important that I know it.

Dennis Allen on 2/14/2011

Very good to know

995751D667 on 2/14/2011

Provided valuable new information many people would not konw

Dexter Jones on 2/14/2011

Great tip about an insidious issue.

Tom Stafford on 2/14/2011

Good info.

Richard H. Lang II on 2/14/2011

Does this problem only affect views or are other objects similarly affected?

Satyabodhi Densmore on 2/14/2011

How can one define a view that will appear in: select * from INFORMATION_SCHEMA.VIEWS where IS_UPDATABLE <> 'NO'

Eric on 2/14/2011

Very enlightening, thank you very much.

37F4DA828D on 2/15/2011

wow - Thanks

Kevin Bayer on 2/15/2011

Good stuff Andy...

Shellz on 2/15/2011

Just a really good presenter!

ANITA on 2/15/2011

Very good demostration!

Michael on 2/15/2011

defiantly a good point!

Murali on 2/16/2011

very very good tip

Murali on 2/16/2011

very very good demonstation

5ED6B0C3B7 on 2/21/2011

Nice gotcha to keep in mind!

Terry on 5/10/2011

I've had this issue before and NEVER found out why until today!!! Thanks for this!!!

Norbert Kessler on 5/23/2011

Same happens with procedures

Stephanie Brown on 6/27/2011

really important information - thanks!

Arturo on 7/13/2011

Great, I don't now that, very useful

Maurice Ivory on 7/22/2011

Good information I did not know about.

Destiny Barnes on 10/26/2011

it would have been nice to see what it means to script out a view. I'm assuming that means copy the sql,but I'm not 100% sure.

arshpreet on 12/5/2011

Can't we use the T-SQL instead for renaming any view: sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'

Samrat Sen on 2/15/2012

small thing but really a powerful one.