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

sp_helptext vw_DatabaseNames

Or

SELECT definition
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) = 'vw_DatabaseNames'

Next we rename the view.

sp_rename 'vw_DatabaseNames','vw_MyDatabaseNames'

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.

sp_helptext vw_MyDatabaseNames

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.