Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

Common SP to Change/Alter User Defined Datatype's(UDDs) Size and Datatype even it has references of function,view,constraint and etc..

CREATE PROCEDURE  Change_UDD_SP
(
    @Find_UDD_Name        NVARCHAR(MAX),
    @Replace_UDD_Type    NVARCHAR(MAX)
)
AS
BEGIN
   
    SET NOCOUNT ON

    DECLARE @Replace_UDD_Name    NVARCHAR(MAX)
    DECLARE @Udd_Count            INT
    DECLARE @CreateUDDQuery        NVARCHAR(MAX)

    SELECT    @Udd_Count            = 1
    SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)

   
    SELECT @Find_UDD_Name=LTRIM(RTRIM(@Find_UDD_Name))
    SELECT @Replace_UDD_Type=LTRIM(RTRIM(@Replace_UDD_Type))
   
    -- TO VALIDATE DATA
    IF ISNULL(@Find_UDD_Name,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Find_UDD_Name',16,1)
        RETURN
    END
   
    IF ISNULL(@Replace_UDD_Type,'')=''
    BEGIN
        RAISERROR('ENTER VALUE FOR @Replace_UDD_Type',16,1)
        RETURN
    END

    -- TO CHECK FIND UDD IS EXISTS
    IF NOT EXISTS(SELECT 'X' FROM sys.types where name=@Find_UDD_Name)
    BEGIN
        RAISERROR('NO SUCH UDD : %s',16,1,@Find_UDD_Name)
        RETURN
    END
   
   
    --IF NOT EXISTS(SELECT    'X'    FROM    SYS.OBJECTS O,
    --                                    SYS.ALL_COLUMNS C,
    --                                    SYS.TYPES T
    --                            WHERE    o.object_id        =    c.object_id 
    --                            AND        c.user_type_id    =    t.user_type_id 
    --                            AND        t.name            =    @Find_UDD_Name)
    --BEGIN
    --    RAISERROR('NO REFERENCES TO THIS UDD : %s',16,1,@Find_UDD_Name)
    --    RETURN
    --END
   

    -- FIND RENAMING UDDs
    WHILE EXISTS(SELECT 'X' FROM SYS.TYPES WHERE name=@Replace_UDD_Name)
    BEGIN
        SELECT    @Udd_Count            = @Udd_Count +1
        SELECT    @Replace_UDD_Name    = @Find_UDD_Name + CONVERT(NVARCHAR(10),@Udd_Count)   
    END
   
   
    BEGIN TRANSACTION
   
    BEGIN TRY

        -- RENAMING UDDs
        EXEC SP_RENAME @Find_UDD_Name,@Replace_UDD_Name
       
        -- CREATING NEW UDD
        SELECT @CreateUddQuery    = N'CREATE TYPE ' + @Find_UDD_Name + ' FROM ' + @Replace_UDD_Type
        EXEC SP_EXECUTESQL @CreateUDDQuery
        --PRINT @CreateUDDQuery


        DECLARE @Table_Name        NVARCHAR(MAX)
        DECLARE @Table_Type        NVARCHAR(MAX)
        DECLARE @Column_Name    NVARCHAR(MAX)
        DECLARE @Is_Nullable    INT
        DECLARE @Is_Identity    INT
        DECLARE @Type_Name        NVARCHAR(MAX)
        DECLARE @Alter_Qry        NVARCHAR(MAX)
        DECLARE @Condition_Qry    NVARCHAR(MAX)
        DECLARE @Slno            INT
        DECLARE @Old_Table_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Name    NVARCHAR(MAX)
        DECLARE @Constrn_Cols    NVARCHAR(MAX)
        DECLARE @Constrn_Qry    NVARCHAR(MAX)
        SELECT    @Old_Table_Name    =    ''
        SELECT    @Constrn_Name    =    ''
        SELECT    @Constrn_Cols    =    ''

        DECLARE Cur_UDD CURSOR FOR    SELECT    o.name,o.type ,c.name,c.is_nullable,c.is_identity,t.name,CASE WHEN o.type='U' THEN 1 ELSE 2 END AS Slno 
                                    FROM    SYS.OBJECTS O,
                                            SYS.ALL_COLUMNS C,
                                            SYS.TYPES T
                                    WHERE    o.object_id        =    c.object_id 
                                    AND        c.user_type_id    =    t.user_type_id 
                                    AND        t.name            =    @Replace_UDD_Name
                                    ORDER BY Slno,o.type,o.name 
                                   

        OPEN Cur_UDD

        FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
        WHILE @@FETCH_STATUS=0
        BEGIN
           
            IF @Table_Name<>@Old_Table_Name AND UPPER(@Table_Type)='U'
            BEGIN
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
                IF EXISTS(SELECT 'X'    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                                        WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                                        AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                                        AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                                        AND        k.TABLE_NAME            =    c.TABLE_NAME
                                        AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                                        AND        k.TABLE_NAME            =    @Table_Name
                                        AND        k.COLUMN_NAME            =    @Column_Name)
                BEGIN
                    SELECT    @Constrn_Name            =    k.CONSTRAINT_NAME
                    FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE k,
                            INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
                    WHERE    k.CONSTRAINT_CATALOG    =    c.CONSTRAINT_CATALOG
                    AND        k.CONSTRAINT_NAME        =    c.CONSTRAINT_NAME
                    AND        k.TABLE_CATALOG            =    c.TABLE_CATALOG
                    AND        k.TABLE_NAME            =    c.TABLE_NAME
                    AND        c.CONSTRAINT_TYPE        =    'PRIMARY KEY'      
                    AND        k.TABLE_NAME            =    @Table_Name
                    AND        k.COLUMN_NAME            =    @Column_Name
                   
                    SELECT    @Constrn_Cols    =    STUFF(CONVERT(NVARCHAR(MAX),(    SELECT    ','+COLUMN_NAME  
                                                                                FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                                                                WHERE    CONSTRAINT_NAME    =    @Constrn_Name
                                                                                FOR        XML PATH(''))),1,1,'')
                    SELECT    @Constrn_Qry=N'ALTER TABLE ' +      @Table_Name + ' DROP CONSTRAINT ' + @Constrn_Name                                           
                    --PRINT @Constrn_Qry
                    EXEC    SP_EXECUTESQL @Constrn_Qry
                    SELECT    @Old_Table_Name    =    @Table_Name
                END
            END
           
            SELECT @Alter_Qry        =''
            SELECT @Condition_Qry    =''
           
            IF UPPER(@Table_Type)='U'
            BEGIN
                IF @Is_Nullable=0
                BEGIN
                    SELECT @Condition_Qry = ' NOT NULL '
                END
                SELECT @Alter_Qry=N'ALTER TABLE ' +  @Table_Name + ' ALTER COLUMN ' +  @Column_Name + ' ' + @Find_UDD_Name + ' ' + @Condition_Qry
               
                --PRINT @Alter_Qry
                EXEC SP_EXECUTESQL @Alter_Qry
               
            END
            ELSE
            BEGIN
                -- TO REFRESH TABLE FUNCTION AND VIEWS
                EXEC sp_refreshsqlmodule @Table_Name
               
            END
           
           
            FETCH NEXT FROM Cur_UDD INTO @Table_Name,@Table_Type,@Column_Name,@Is_Nullable,@Is_Identity,@Type_Name,@Slno
            -- CHECKING NEXT RECORD
            IF (@Table_Name<>@Old_Table_Name OR @@FETCH_STATUS<>0)  AND @Constrn_Name<>''
            BEGIN
                SELECT    @Constrn_Qry=N'ALTER TABLE ' + @Old_Table_Name + ' ADD CONSTRAINT ' + @Constrn_Name + ' PRIMARY KEY('+@Constrn_Cols+')'
                --PRINT @Constrn_Qry
                EXEC SP_EXECUTESQL @Constrn_Qry
                SELECT    @Constrn_Name    =    ''
                SELECT    @Constrn_Cols    =    ''
            END
        END

        CLOSE        Cur_UDD
        DEALLOCATE    Cur_UDD
       
        -- CHECKING OTHER REF FOR THIS UDD
        DECLARE @Ref_Name    NVARCHAR(MAX)
        DECLARE Cur_Ref CURSOR FOR SELECT DISTINCT SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS WHERE USER_DEFINED_TYPE_NAME    =    @Replace_UDD_Name
        OPEN    Cur_Ref
        FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        WHILE @@FETCH_STATUS=0
        BEGIN
            EXEC sp_refreshsqlmodule @Ref_Name
            FETCH NEXT FROM Cur_Ref INTO @Ref_Name
        END
        CLOSE        Cur_Ref
        DEALLOCATE    Cur_Ref
       
       
        -- DROPPING DUMMY UDD
        SELECT @CreateUddQuery    = N'DROP TYPE ' + @Replace_UDD_Name
        EXEC SP_EXECUTESQL @CreateUDDQuery
        COMMIT TRANSACTION
   
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
       
        IF (SELECT CURSOR_STATUS('global','Cur_UDD')) >=0
        BEGIN
            DEALLOCATE    Cur_UDD
        END
       
        IF(SELECT CURSOR_STATUS('global','Cur_Ref'))>=0
        BEGIN
            DEALLOCATE Cur_Ref
        END
       
        DECLARE @Err_Msg NVARCHAR(MAX)
        DECLARE @Err_Sev INT
        SELECT  @Err_Msg=ERROR_MESSAGE(),@Err_Sev=ERROR_SEVERITY()   
        RAISERROR(@Err_Msg,16,1)
    END CATCH
   
    SET NOCOUNT OFF

END