SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[P_DeleteColumnWithConstraint]
@tbname nvarchar(180), --表名
@fdname nvarchar(180) --字段名
AS
BEGIN
DECLARE @sqlCmd nvarchar(1024);
-- 定义游标.
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;
--判断列是否存在
IF COL_LENGTH(@tbname, @fdname) IS not NULL
begin
exec('alter table ['+@tbname+'] drop column ['+@fdname+']')
end
END
GO
06-05
2135
10-07
1989