用户定义的数据类型

---

　　用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。例如，可定义一种称为 　　postal_code 的数据类型，它基于 Char 数据类型。
　　当创建用户定义的数据类型时，必须提供三个数：数据类型的名称、所基于的系统数据类型和数据类型的可空性。 
--///<summary>（1）创建用户定义的数据类型 </summary>///

　　创建用户定义的数据类型可以使用 Transact-SQL 语句。系统存储过程 sp_addtype 可以来创建用户定义的数据类型。其语法形式如下：
　　sp_addtype {type},[,system_data_bype][,'null_type']
　　其中，type 是用户定义的数据类型的名称。system_data_type 是系统提供的数据类型，例如 Decimal、Int、Char 　　等等。 null_type 表示该数据类型是如何处理空值的，必须使用单引号引起来，例如'NULL'、'NOT NULL'或者'NONULL'。
　　例子：
　　Use cust
　　Exec sp_addtype ssn,'Varchar(11)',"Not Null'
　　创建一个用户定义的数据类型 ssn，其基于的系统数据类型是变长为11 的字符，不允许空。
　　例子：
　　Use cust
　　Exec sp_addtype birthday,datetime,'Null'
　　创建一个用户定义的数据类型 birthday，其基于的系统数据类型是 DateTime，允许空。
　　例子：
　　Use master
　　Exec sp_addtype telephone,'varchar(24),'Not Null'
　　Eexc sp_addtype fax,'varchar(24)','Null'
　　创建两个数据类型，即 telephone 和 fax 
--///<summary>（2）删除用户定义的数据类型 </summary>///

　　当用户定义的数据类型不需要时，可删除。删除用户定义的数据类型的命令是 sp_droptype {'type'}。
　　例子：
　　Use master
　　Exec sp_droptype 'ssn'
　　注意：当表中的列还正在使用用户定义的数据类型时，或者在其上面还绑定有默认或者规则时，这种用户定义的数据类型不能删除。 
--出处： 
--责任编辑：51windows

---

  --///<summary>修改用户定义的数据类型<summary>///
  --///作者：未知 来源：月光软件站 加入时间：2005-6-5　月光软件站///
  
  --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)'