SQL 2005 - sp_rename doesn't work on user defined data types

Question

First a little intro, the company I work for relies heavily on user defined data types for the object model we have set up. This gave us maximum flexibility to for example easily extend descriptions to a varchar(50) instead of a varchar(40) when a customer required this.
The process of changing the datatype is first we rename the datatype to have a new name, and afterwards the new datatype is rebound  to all tables, stored procedures etc...

however, in SQL 2005, one of the first steps, namely renaming the datatype fails, in fact it appears you can no longer rename user defined datatypes if they are used within stored procedures. A little example which works like a charm in previous SQL versions:
EXEC sp_addtype 'd_test' , 'varchar(11)', 'NOT NULL', 'dbo'
GO
CREATE PROCEDURE p_test (@parameter d_test) AS
SELECT @parameter
GO
EXEC sp_rename 'd_test','d_test_bak'

In SQL 2005 this generates the following error:
Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 3721, Level 16, State 1, Procedure sp_rename, Line 484
Type 'd_test' cannot be renamed because it is being referenced by object 'p_test'.


It seems only a problem when a procedure references the datatype, references to tables are no problem, the following for example does work:
EXEC sp_addtype 'd_test2' , 'varchar(11)', 'NOT NULL', 'dbo'
GO
CREATE TABLE t_test (a_field d_test2 NULL)
EXEC sp_rename 'd_test2','d_test2_bak'

This gives the message:
Caution: Changing any part of an object name could break scripts and stored procedures.
The userdatatype was renamed to 'd_test2_bak'.

Any idea if there's a way to circumvent the error on sp_rename if the data-type is being used within a procedure? (and dropping the procedures is not really an option, some data-types reference more than 3000 stored procedures...  )

Answer 1

I have filed a bug identifying this problem as a backward compatibility issue. We will consider fixing this for the next service pack or release. You can also file a bug using the MSDN Product Feedback Center if you want so others can vote on this issue. For now, there is no way to suppress the behavior of sp_rename. You will have to drop and recreate the SP. (maybe use the scripting features of SMO or DMO to simplify this)