如何drop有default constraint的column - SQL

最新推荐文章于 2022-05-06 14:04:15 发布

东方匠心

最新推荐文章于 2022-05-06 14:04:15 发布

阅读量876

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： object table sql command string date

本文链接：https://blog.youkuaiyun.com/tastelife/article/details/5899023

数据库专栏收录该内容

63 篇文章

订阅专栏

本文介绍了一次SQL Server中表结构调整的过程，包括修改UPTIME和NEWTIME字段为非空的datetime2类型，并删除了NEWTIME字段上的默认约束。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [MK_UboxChs]
GO

/****** Object: Table [dbo].[ACCOUNT_ITEM_TBL]    Script Date: 09/21/2010 09:43:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter TABLE [dbo].[ACCOUNT_ITEM_TBL]
alter column UPTIME datetime2 NOT NULL
go

declare @default sysname, @sql nvarchar(max)
-- get name of default constraint
select @default = name
from sys.default_constraints
where parent_object_id = object_id('ACCOUNT_ITEM_TBL')
AND type = 'D'
AND parent_column_id = (
    select column_id
    from sys.columns
    where object_id = object_id('ACCOUNT_ITEM_TBL')
    and name = 'NEWTIME'
)
-- create alter table command as string and run it
set @sql = N'alter table ACCOUNT_ITEM_TBL drop constraint ' + @default
exec sp_executesql @sql
go

alter TABLE [dbo].[ACCOUNT_ITEM_TBL]
alter column NEWTIME datetime2 NOT NULL
go