1、要求:将表NIS_HLZK_ZKJH_MX_JC中的字段JSFS的默认值由0改成1
2、修改SQL:
if exists(select 1 from sysobjects where id=object_id('NIS_HLZK_ZKJH_MX_JC') and type = 'U')
BEGIN
if exists(select 1 from syscolumns where name='JSFS' and id=object_id('NIS_HLZK_ZKJH_MX_JC'))
BEGIN
-- 获取约束名称
DECLARE @NAME nvarchar(100)
DECLARE @TB_NAME nvarchar(100)
SET @TB_NAME = 'NIS_HLZK_ZKJH_MX_JC'
set @NAME=''
SELECT @NAME = @NAME + [name] from sysobjects t
where id = (select cdefault from syscolumns where id = object_id(N'NIS_HLZK_ZKJH_MX_JC')
and name='JSFS')
--删除默认值之前的约束
SELECT @NAME
DECLARE @ALTERSQL NVARCHAR(MAX)
SET @ALTERSQL=N'ALTER TABLE '+@TB_NAME+'
DROP CONSTRAINT '+@NAME + ''
select @ALTERSQL
exec sp_executesql @ALTERSQL
--添加默认值
ALTER TABLE NIS_HLZK_ZKJH_MX_JC
ADD default 1 for JSFS --计算方式:0 平均扣分 1累计扣分,默认累计扣分
END
END
GO