Modify user-defined data types

-- The custom type of SQLSERVER is easy to use. However, once the data type is referenced, It is a headache to modify the data type. This stored procedure is designed to deal with it. -- Sp_rebuildallview see other pages in this BLOG: createproceduresp_rechangfieldtype (@ typenamevarchar (50), @ newtypev

-- The custom type of SQLSERVER is easy to use. However, once the data type is referenced, It is a headache to modify the data type. This stored procedure is designed to deal with it. -- Sp_rebuildallview see other pages in this BLOG create procedure sp_rechangfieldtype (@ typename varchar (50), @ newtype v

-- The custom type of SQLSERVER is easy to use. However, once the data type is referenced, It is a headache to modify the data type. This stored procedure is designed to deal with it.

-- Sp_rebuildallview: see other pages in this BLOG.
Create procedure sp_rechangfieldtype (@ typename varchar (50), @ newtype varchar (50 ))
As
Begin

Declare @ typeid int
Declare @ tablename varchar (50)
Declare @ column varchar (50)

Declare @ sqlstr varchar (200)
Declare @ defaultid int

Select @ typeid = xusertype
From policypes
Where name = @ typename and xusertype> 256
AND (is_member ('db _ owner') = 1 OR is_member ('db _ ddladmin') = 1 OR is_member (user_name (uid) = 1)

Declare mycursor cursor
Select o. name, c. name, c. cdefault
From syscolumns c, policypes t, sysusers u, sysobjects o
Where c. xusertype = @ typeid
And t. xusertype = @ typeid
And o. uid = u. uid
And c. id = o. id
And o. type = 'U'

Open mycursor
Fetch next from mycursor into @ tablename, @ column, @ defaultid
While @ fetch_status = 0
Begin
If @ defaultid <> 0
Begin
Set @ sqlstr = 'alter table' + @ tablename + 'drop' + object_name (@ defaultid)
Exec (@ sqlstr)

Set @ sqlstr = 'alter table' + @ tablename + 'alter column' + @ column + ''+ @ newtype
Exec (@ sqlstr)

-- Set @ sqlstr = 'alter table' + @ tablename + 'add contraint' + @ tablename + 'df' + @ column + 'default 0'

End
Else
Begin
Set @ sqlstr = 'alter table' + @ tablename + 'alter column' + @ column + ''+ @ newtype

Print @ sqlstr
Exec (@ sqlstr)
End
-- If @ error <> 0
-- Continue
Fetch next from mycursor into @ tablename, @ column, @ defaultid
End
-- If there are no constraints, you can delete them directly. If there are constraints. Handle constraints first.

Close mycursor
Deallocate mycursor

End

GO

Create procedure SP_CHANGEFIELD (@ oldtypename varchar (50), @ newdtype varchar (50 ))
As
Begin

Exec ('SP _ addtype U_LOCALTYPE, ''' + @ newdtype + '''')

Exec SP_rechangfieldtype @ OLDTYPENAME, 'U _ localtype'

EXEC sp_rebuildallview

EXEC ('SP _ droptype '+ @ OLDTYPENAME)

EXEC ('SP _ addtype' + @ OLDTYPENAME + ', ''' + @ newdtype + '''')

Exec SP_rechangfieldtype 'U _ localtype', @ OLDTYPENAME

EXEC sp_rebuildallview

EXEC sp_droptype 'U _ localtype'

End
GO

-- The following is an example. Change the U_HELLO length to 30

SP_ADDTYPE U_HELLO, 'varchar (10 )'

GO
Create table testtype (NAME U_HELLO)
GO

SP_CHANGEFIELD 'U _ hello', 'varchar (30 )'