SQL SERVER 修改带有默认值约束的列类型

SQL SERVER 修改带有默认值约束的列类型时，由于默认值约束导致执行sql失败，需要先删除默认值约束，修改类型，在加上默认值约束。

在建表时加默认值创建的默认值约束由于名称不知道没法直接drop通过以下语句直接完成修改类型操作

DECLARE @dfname NVARCHAR(50),
        @dropsql NVARCHAR(100)
SELECT @dfname=NAME FROM sysobjects WHERE id = (SELECT syscolumns.cdefault FROM sysobjects INNER JOIN syscolumns ON sysobjects.Id=syscolumns.Id 
    WHERE upper(SYSCOLUMNS.NAME) = '@tablecolumn' AND upper(SYSOBJECTS.NAME) = '@table')
    IF LEN(@dfname)>0
    SET @dropsql='ALTER TABLE @table DROP CONSTRAINT '+ CAST(@dfname AS NVARCHAR(50))
    IF LEN(@dfname)>0
    EXEC sp_executesql @dropsql
GO
IF  EXISTS (SELECT 1  FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.ID = SYSCOLUMNS.ID 
    WHERE upper(SYSCOLUMNS.NAME) = '@tablecolumn' AND upper(SYSOBJECTS.NAME) = '@table')
ALTER TABLE @table ALTER COLUMN @tablecolumn float 
GO
IF  EXISTS (SELECT 1  FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.ID = SYSCOLUMNS.ID 
    WHERE upper(SYSCOLUMNS.NAME) = '@tablecolumn' AND upper(SYSOBJECTS.NAME) = '@table')
ALTER TABLE @table add default 0  for @tablecolumn
GO

 如果默认值约束是制定名称的就比较简单；先根据名称直接drop就可以

IF  EXISTS (SELECT 1  FROM sysobjects WHERE upper(NAME) = '@dfname' AND id = (SELECT syscolumns.cdefault FROM sysobjects INNER JOIN syscolumns ON sysobjects.Id=syscolumns.Id 
    WHERE upper(SYSCOLUMNS.NAME) = '@tablecolumn' AND upper(SYSOBJECTS.NAME) = '@table')
ALTER TABLE @table DROP CONSTRAINT @dfname
GO
IF  EXISTS (SELECT 1  FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.ID = SYSCOLUMNS.ID 
    WHERE upper(SYSCOLUMNS.NAME) = '@tablecolumn' AND upper(SYSOBJECTS.NAME) = '@table')
ALTER TABLE @table ALTER COLUMN @tablecolumn float 
GO
IF  EXISTS (SELECT 1  FROM SYSCOLUMNS INNER JOIN SYSOBJECTS ON SYSOBJECTS.ID = SYSCOLUMNS.ID 
    WHERE upper(SYSCOLUMNS.NAME) = '@tablecolumn' AND upper(SYSOBJECTS.NAME) = '@table')
ALTER TABLE @table add @dfname default 0  for @tablecolumn
GO

 

转载于:https://www.cnblogs.com/zhutao1015/p/6738827.html