修改MsSql有默認值約束列類型
DROP TABLE TMP
CREATE TABLE TMP
(
ID INT,
TEST VARCHAR(10) DEFAULT ''
)
GO
INSERT INTO TMP(ID) SELECT 1 UNION SELECT 2 UNION SELECT 3
INSERT INTO TMP(ID, TEST) SELECT 4, 'AB' UNION SELECT 5, 'CD'
GO
DECLARE @ConstraintName varchar(200)
DECLARE @SQLSTR varchar(2000)
SELECT @ConstraintName=OBJECT_NAME(constid) FROM syscolumns AS A LEFT JOIN sysCONSTRAINTs AS B
ON A.ID=B.ID AND A.colid=B.colid
--WHERE object_name(a.id)= '表名 'and a.name= '列名'
WHERE object_name(a.id)= 'TMP 'and a.name= 'TEST'
SET @SQLSTR='ALTER TABLE TMP DROP CONSTRAINT '+@ConstraintName
EXEC(@SQLSTR)
GO
UPDATE TMP SET TEST=NULL
ALTER TABLE TMP ALTER COLUMN TEST INT
CREATE TABLE TMP
(
ID INT,
TEST VARCHAR(10) DEFAULT ''
)
GO
INSERT INTO TMP(ID) SELECT 1 UNION SELECT 2 UNION SELECT 3
INSERT INTO TMP(ID, TEST) SELECT 4, 'AB' UNION SELECT 5, 'CD'
GO
DECLARE @ConstraintName varchar(200)
DECLARE @SQLSTR varchar(2000)
SELECT @ConstraintName=OBJECT_NAME(constid) FROM syscolumns AS A LEFT JOIN sysCONSTRAINTs AS B
ON A.ID=B.ID AND A.colid=B.colid
--WHERE object_name(a.id)= '表名 'and a.name= '列名'
WHERE object_name(a.id)= 'TMP 'and a.name= 'TEST'
SET @SQLSTR='ALTER TABLE TMP DROP CONSTRAINT '+@ConstraintName
EXEC(@SQLSTR)
GO
UPDATE TMP SET TEST=NULL
ALTER TABLE TMP ALTER COLUMN TEST INT
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步