数据库删除列

先删除约束。  
   
  --删除指定表中的指定列的约束  
  CREATE             PROCEDURE   P_DelConstraint    
   
  @table_name   varchar(50), --表名  
  @col_name   varchar(50) --列名  
   
  AS  
  declare   @tbname   sysname,@fdname   sysname  
  select   @tbname=@table_name --要处理的表名  
  ,@fdname=@col_name --要处理的字段名  
   
  --定义删除处理的语句  
  declare   tb   cursor   local   for  
  --默认值约束  
  select   sql='alter   table   ['+b.name+']   drop   constraint   ['+d.name+']'  
  from   syscolumns   a  
  join   sysobjects   b   on   a.id=b.id   and   a.name=@fdname   and   b.name=@tbname    
  join   syscomments   c   on   a.cdefault=c.id  
  join   sysobjects   d   on   c.id=d.id  
  union --外键引用  
  select   s='alter   table   ['+c.name+']   drop   constraint   ['+b.name+']'  
  from   sysforeignkeys   a  
  join   sysobjects   b   on   b.id=a.constid  
  join   sysobjects   c   on   c.id=a.fkeyid  
  join   syscolumns   d   on   d.id=c.id   and   a.fkey=d.colid   and   d.name=@fdname  
  join   sysobjects   e   on   e.id=a.rkeyid   and   e.name=@tbname  
  join   syscolumns   f   on   f.id=e.id   and   a.rkey=f.colid    
  union --主键/唯一键/索引  
  select   case   when   e.xtype   in('PK','UQ')   then   'alter   table   ['+c.name+']   drop   constraint   ['+e.name+']'  
  else   'drop   index   ['+c.name+'].['+a.name+']'   end  
  from   sysindexes   a  
  join   sysindexkeys   b   on   a.id=b.id   and   a.indid=b.indid  
  join   sysobjects   c   on   b.id=c.id   and   c.xtype='U'   and   c.name=@tbname  
  join   syscolumns   d   on   b.id=d.id   and   b.colid=d.colid   and   d.name=@fdname  
  left   join   sysobjects   e   on   e.id=object_id(a.name)  
  where   a.indid   not   in(0,255)  
   
  --执行删除  
  declare   @s   varchar(8000)  
  open   tb  
  fetch   next   from   tb   into   @s  
  while   @@fetch_status=0  
  begin  
  exec(@s)  
  fetch   next   from   tb   into   @s  
  end  
  close   tb  
  deallocate   tb  
   
   
   
   
  GO