sql server 删除表字段和字段的约束

 

删除数据库表中的字段时,使用了 :alter table 表名 drop column 列名

服务器返回的错误为:
Server: Msg 5074, Level 16, State 1, Line 1
The object 约束名 is dependent on column 列名.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN 列名 failed because one or more objects access this column.

 

 

解决方法是先删除所有关于这一列的约束,然后在开始删除这一列

下面的代码为删除表 B_File 中的 Name 列。

 

DECLARE  @sqlCmd nvarchar(1024);
DECLARE  @tbname nvarchar(180), --要处理的表名
        @fdname nvarchar(180), --要处理的字段名
        @delfield bit=1  --0只删除关系,1同时删除字段

set @tbname=B_File;
set  @fdname= Name;

 BEGIN
   -- 定义游标.
   DECLARE c_test_main CURSOR FAST_FORWARD 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)

     
   -- 打开游标.
   OPEN c_test_main;
   --填充数据.
   FETCH NEXT FROM c_test_main INTO @sqlCmd;
   --假如检索到了数据,才处理.
   WHILE @@fetch_status = 0
   BEGIN
     PRINT @sqlCmd;
     exec sp_executesql @sqlcmd;--执行约束的删除操作

     --填充下一条数据.
     FETCH NEXT FROM c_test_main INTO @sqlCmd;
   END;
   -- 关闭游标
   CLOSE c_test_main;
   --释放游标.
   DEALLOCATE c_test_main;
 END;
 if @delfield=1 
 begin

 --判断表是否存在
 --if exists (select 1 from sys.tables where name=@tbname and type = ‘u‘)  

 --判断列是否存在
 IF COL_LENGTH(@tbname, @fdname) IS not NULL
 exec(alter table [+@tbname+] drop column [+@fdname+])
 end

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。