Something to watch out for when using sp_rename on Stored Procedures, Functions and Views6
February 11, 2013 by Kenneth Fisher
For those who don’t know, the stored procedure sp_rename allows you to change the name of a user-created object. Basically when you use sp_rename it changes the name of an object in the catalog tables. Unfortunately on a stored procedure, function or view this isn’t the only place the name is stored. These objects have the code used to create them stored in system tables as well as just their name. The code can be exposed in several ways. The system view sys.sql_modules and the system stored procedure sp_helptext are a couple of good examples.
When using sp_rename to change the name of one of these types of objects the code isn’t changed along with the name in the system tables.
You may be wondering why that matters. Well, recently I was optimizing a view and I scripted out the code using sp_helptext. First I optimized the query, then changed the CREATE to an ALTER. I was rather confused when I ran the script and kept getting errors saying the view didn’t exist. I spent probably 5-10 minutes fighting it before I realized what had happened.
Here is a quick demonstration to show what I’m talking about.
CREATE VIEW vw_DatabaseNames AS SELECT name FROM sys.databases
Once the view is created the code for it can be exposed by using either
SELECT definition FROM sys.sql_modules WHERE OBJECT_NAME(object_id) = 'vw_DatabaseNames'
Next we rename the view.
Which returns the standard warning:
Caution: Changing any part of an object name could break scripts and stored procedures.
Now we will wait 5 or 6 months until we have completely forgotten about this.
We just realized this script will run better if we exclude tempdb, master, model and msdb. So we script it out so that we can make the change.
Text ----------------------------------------------------------------------------- CREATE VIEW vw_DatabaseNames AS SELECT name FROM sys.databases
We add the WHERE clause and change the CREATE to an ALTER.
ALTER VIEW vw_DatabaseNames AS SELECT name FROM sys.databases WHERE name NOT IN ('tempdb','master','model','msdb')
When we execute the script though we get the following error.
Msg 208, Level 16, State 6, Procedure vw_DatabaseNames, Line 3 Invalid object name 'vw_DatabaseNames'.
But wait, we know the object exists, we just scripted it out! We can look in the Object Explorer, sysobjects etc and it’s there. Of course those who are more observant than I was will notice that the error is for vw_DatabaseNames, not vw_MyDatabaseNames. If you look a bit farther up you will also notice that the output from sp_helptext was CREATE VIEW vw_DatabaseNames.
The only time the issue seems to come up is when using the system views and stored procedures that expose the code stored in the system tables. If we had used Object Explorer to script out the view, or used the view designer then there would have been no problem. In fact one way I found to correct the issue was to open the view up in the view designer and save. Same applies of course to stored procedures, functions etc.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: code language, language sql, microsoft sql server, sql statements, system functions, T-SQL
6 thoughts on “Something to watch out for when using sp_rename on Stored Procedures, Functions and Views”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Try this with 2012 version!:
select top (20) name as lastModification,create_date as createdIn from sys.objects where name like ‘%vw%’
order by name desc
Good link, although I disagree with the idea that the best method is drop and re-create. That has some security implications if permissions were granted explicitly on the stored procedure. I find it’s better to right click on the object in Object Explorer and use rename.
Hi, another implication I found when renaming views/stored procedures is the problems it causes when you try and regenerate a copy of the database by scripting the definitions. The script will reference the old object name and not its current renamed name.
e.g. you have a view called [vw_get_details] and you decide to rename this to [zz_vw_getdetails]. The ‘sys.system_modules’ definition will still contain the definition ‘CREATE VIEW [vw_get_details] AS …’ so this is what will be scripted out and be loaded into your target system.
Very good point.
[…] first thing is that if you change the name of a stored procedure, function, etc you aren’t changing the name as it…. That can be a real pain, but that’s not where the fun part of renaming comes […]