修改用户定义的数据类型

来源：互联网发布：大的淘宝店铺转让平台编辑：程序博客网时间：2024/05/16 06:39

--SQLSERVER的自定义类型比较好用吧，但是，一旦引用该数据类型后，想修改数据类型，就是一大头疼的事了，本存储过程就是专门对付它的。

--sp_rebuildallview见本BLOG中的其它页面
createproceduresp_rechangfieldtype(@typenamevarchar(50),@newtypevarchar(50))
as
begin

declare@typeidint
declare@tablenamevarchar(50)
declare@columnvarchar(50)

declare@sqlstrvarchar(200)
declare@defaultidint

select@typeid=xusertype
fromsystypes
wherename=@typenameandxusertype>256
AND(is_member('db_owner')=1ORis_member('db_ddladmin')=1ORis_member(user_name(uid))=1)

declaremycursorcursorfor
selecto.name,c.name,c.cdefault
fromsyscolumnsc,systypest,sysusersu,sysobjectso
wherec.xusertype=@typeid
andt.xusertype=@typeid
ando.uid=u.uid
andc.id=o.id
ando.type='u'

openmycursor
fetchnextfrommycursorinto@tablename,@column,@defaultid
while@@fetch_status=0
begin
if@defaultid<>0
begin
set@sqlstr='altertable'+@tablename+'drop'+object_name(@defaultid)
exec(@sqlstr)

   set@sqlstr='altertable'+@tablename+'altercolumn'+@column+''+@newtype
   exec(@sqlstr)

--   set@sqlstr='altertable'+@tablename+'addcontraint'+@tablename++'default0'

end
else
begin
set@sqlstr='altertable'+@tablename+'altercolumn'+@column+''+@newtype

print@sqlstr
exec(@sqlstr)
end
--if@@error<>0
-- continue
fetchnextfrommycursorinto@tablename,@column,@defaultid
end
--如果没有约束，则可以直接删除。如果有约束。先处理约束。

closemycursor
deallocatemycursor

end

GO

createprocedureSP_CHANGEFIELD(@OLDTYPENAMEVARCHAR(50),@NEWDTYPEVARCHAR(50))
as
begin

exec('sp_addtypeU_LOCALTYPE,'''+@newdtype+'''')

execSP_rechangfieldtype@OLDTYPENAME,'U_LOCALTYPE'

EXECsp_rebuildallview

EXEC('sp_droptype'+@OLDTYPENAME)

EXEC('sp_addtype'+@OLDTYPENAME+','''+@newdtype+'''')

execSP_rechangfieldtype'U_LOCALTYPE',@OLDTYPENAME

EXECsp_rebuildallview

EXECsp_droptype'U_LOCALTYPE'

end
GO

--以下是示例。将U_HELLO的长度改为30

SP_ADDTYPEU_HELLO,'VARCHAR(10)'

GO
CREATETABLETESTTYPE(NAMEU_HELLO)
GO

SP_CHANGEFIELD'U_HELLO','VARCHAR(30)'