Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Something to watch out for when using sp_rename on Stored Procedures, Functions and Views

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.


Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...