--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 = @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 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 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
--如果没有约束，则可以直接删除。如果有约束。先处理约束。
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
--以下是示例。将U_HELLO的长度改为 30
SP_ADDTYPE U_HELLO, ''VARCHAR(10)''
GO
CREATE TABLE TESTTYPE(NAME U_HELLO)
GO
SP_CHANGEFIELD ''U_HELLO'', ''VARCHAR(30)''
关注此文的读者还看过：
- 2010-4-1 18:12:45 预防非法表D99_Tmp,kill_kk的建立
- 2008-4-23 15:53:39 SQL Server数据库超级管理员账号防护知识
- 2008-4-11 21:37:35 SQL Server 2005数据库镜像配置脚本示例
- 2008-4-4 9:12:54 讲解如何实现互联网上数据库的安全
- 2008-4-1 11:07:24 SQL 2005数据库转到SQL 2000的步骤讲解
- 2008-3-31 12:39:45 SQL Server自动生成日期加数字的序列号
- 2008-3-31 12:39:07 SQL Server 2000的数据库容量究竟是多大
- 2008-3-18 18:01:49 SQL Server 2005数据库的同义词Bug 讲解
|
