Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

SQL Server Quirks - sp rename

From Wiki

Jump to: navigation, search

I've recently run into one of those fun little "quirks" in SQL Server that makes it oh so fun. I recently made some modifications to primary keys in one of my databases, so I had to go through a list of views that included the altered tables and rewrite them. I noticed that some of the views had a different name when I scripted them out than they did in the object browser.

I ran a few checks to make sure I was in fact looking at the right view, and proceeded to change them, but I was still curious as to why this would happen. Discussing this with a friend, he pointed this out to me from BOL:

Important : After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.

Stored procedures and views can be dropped and re-created quickly because neither object stores data. For best results renaming textual objects, drop and re-create the object by its new name.

So I guess I have never run into this problem, because I always drop and recreate when I need to change the name of a view or stored procedure. But still, the problem is out there. If you don't want to take my word for it, run this query:

  1. --create a test proc
  2. Create Proc test_rename As Select 1
  3. go
  4. --rename it
  5. sp_rename 'test_rename', 'test_rename_modified', 'OBJECT'
  6. go
  7. --examine Routine Definition for new proc name (it still lists as old proc name)
  8. Select Routine_Definition From Information_Schema.Routines Where Specific_Name = 'test_rename_modified'
  9. go
  10. --drop the test proc
  11. Drop Proc test_rename_modified

You can also replace "Proc" with "View", and change the system view you're querying, and see similar results:

  1. --create a sample view
  2. Create view test_rename As Select 1 as T
  3. go
  4. --rename the view
  5. sp_rename 'test_rename', 'test_rename_modified', 'OBJECT'
  6. go
  7. --check the View Definition for new view name (it still lists the old view name)
  8. Select View_Definition From Information_Schema.Views Where TABLE_NAME = 'test_rename_modified'
  9. go
  10. --drop sample view
  11. Drop view test_rename_modified

To avoid the confusion I experienced, it would probably be a good idea to get into the habit of renaming your views and procs by dropping and recreating, rather than using sp_rename.

--AlexCuse 15:42, 31 January 2008 (GMT)

108 Rating: 2.3/5 (73 votes cast)