How to change or alter a user defined data type?

Hi all,
I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
and it's ok for the table/columns but I can NOT drop my user defined data
type using sp_droptype because it's already used in some Stored procs :(
Is there a solution WITHOUT a "delete & re-create" of these stored procs ?
Lilian.This example should demonstrate how to swap out the type. Note that the
rename affects tables, but not stored procs (so you won't have to recompile
your procedures, just hope they don't get called during the brief period
where the type doesn't exist).
-- originally, we create a datatype called fax,
-- and made it able to accept 32 characters
EXEC sp_addType 'fax', 'VARCHAR(32)'
GO
-- so we created a table that uses this datatype
CREATE TABLE dbo.foobar0
(
[fax] fax
)
GO
-- and a simple stored procedure as well
CREATE PROC dbo.foobar1
@.fax fax
AS
BEGIN
DECLARE @.fax2 fax
END
GO
-- now, we realize that fax numbers on
-- jupiter can contain 64 characters, so we
-- have to increase the size of the UDT
-- but we can't just alter the type, and we
-- can't drop it and re-create it either, without
-- altering or dropping / re-creating tables,
-- stored procedures, etc. that use the UDT
-- but we can use a little rename trick to
-- create an interim UDT that meets our needs
-- first, rename the existing UDT. This will
-- change table definitions but it will not alter
-- the text for a procedure / function
EXEC sp_rename 'fax', 'oldfax', 'USERDATATYPE'
GO
-- let's just make sure that it affected our table
-- but not our procedure:
EXEC sp_help foobar0
EXEC sp_helptext foobar1
GO
-- okay, now let's add the larger fax UDT back
-- into the system
EXEC sp_addtype 'fax', 'VARCHAR(64)'
GO
-- alter any tables / views that reference the old
-- fax UDT
ALTER TABLE foobar0 ALTER COLUMN [fax] fax
GO
-- now we should be able to drop the interim
-- UDT:
EXEC sp_droptype 'oldfax'
GO
-- (now let's clean up my silly example)
DROP TABLE dbo.foobar0
DROP PROCEDURE dbo.foobar1
EXEC sp_droptype 'fax'
GO
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:#pSUDo9PDHA.2036@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
> and it's ok for the table/columns but I can NOT drop my user defined data
> type using sp_droptype because it's already used in some Stored procs :(
> Is there a solution WITHOUT a "delete & re-create" of these stored procs
?
> Lilian.
>|||Sorry, you will have to recompile stored procedures that use the datatype as
in/out parameters, but not those that only use the type for local
variables...|||Very interesting...but do you "recompile" a stored proc by script ?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:elXMq29PDHA.2676@.TK2MSFTNGP10.phx.gbl...
> Sorry, you will have to recompile stored procedures that use the datatype
as
> in/out parameters, but not those that only use the type for local
> variables...
>