快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

千赢qy88官网:修改用户定义的数据类型



--SQLSERVER的自定义类型对照好用吧,然则,一旦引用该数据类型后,想改动数据类型,便是一大年夜头疼的事了,本存储历程便是专门对于它的。

--sp_rebuildallview 见本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 systypes

where name 千赢qy88官网= @typename and xusertype > 256

AND (is_member('db_owner') = 1 OR is_member('db_ddladmin') = 1 OR is_member(user_name(千赢qy88官网uid))=1)

declare mycursor cursor for

select o.name, c.name, c.cdefault

from syscolumns c, systypes 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千赢qy88官网 next from mycursor into @tablename, @column, @defaultid

while @@fetch_status = 0

begin

if @defaultid0

--continue

fetch next from mycursor into @tablename, @column, @defaultid

end

--假如没有约束,则可以直接删除。假如有约束。先处置惩罚约束。

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千赢qy88官网 SP_rechangfieldtype 'U_LOCALTYPE', @OLDTYPENAME

EXEC sp_rebuildallview

EXEC sp_droptype 'U_LOCALTYPE'

end

GO

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

SP_ADDTYPE U_HELLO千赢qy88官网, 'VARCHAR(10)'

GO

CREATE TABLE TESTTYPE(NAME U_HELLO)

GO

SP_CHANGEFIELD 'U_HELLO', 'VARCHAR(30)'

免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。

您可能还会对下面的文章感兴趣: