通用刪除字段(具有約束) sql

declare @defname varchar(100)
declare @cmd varchar(500)
declare @tablename varchar(100)
declare @columnname varchar(100)

set @tablename='ALLBankOutDebtAmt'
set @columnname='PerOutInterestAmt'

select @defname = name
FROM sysobjects so
JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = @tablename
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid
FROM syscolumns
WHERE id = object_id(@tablename)
AND name = @columnname)

select @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname
if @cmd is null print 'No default constraint to drop'
exec (@cmd)

select @cmd='alter table '+ @tablename+ ' drop column  '+ @columnname
if @cmd is null print 'No column to drop'
exec (@cmd)