在修改数据类型的时候,会提示存在约束,不能更新,需要先删除约束,然后更新数据类型,在将约束增加回来
alter table A alter column Name nvarchar(100)
- 查找约束名字,并得到添加或者删除语句,可以复制出来运行
SELECT [表名] = tab.name,
[列名] = col.name,
[Default约束名] = df.name,
df.definition,
' ALTER TABLE [dbo].[' + tab.name + '] ADD CONSTRAINT [' + df.name + '] DEFAULT ' + df.definition + ' FOR ['
+ col.name + '] ' + '; ' AS AddSql,
' ALTER TABLE ' + tab.name + ' DROP CONSTRAINT ' + df.name + ' ; ' AS DropSql
FROM sys.default_constraints df
JOIN sys.tables tab
ON (df.parent_object_id = tab.object_id)
JOIN sys.columns col
ON (
df.parent_object_id = col.object_id
AND df.parent_column_id = col.column_id
);
- 依次执行语句
ALTER TABLE A DROP CONSTRAINT DF__FPAMS_ASS__MODEL__4496C3F4 ;
ALTER TABLE A ALTER COLUMN Name nvarchar(200)
ALTER TABLE A ADD CONSTRAINT [DF__FPAMS_ASS__MODEL__4496C3F4] DEFAULT ('0') FOR [Name]