SQLSERVER操作字段约束，修改字段名称等

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

-- 表加注释
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'注释内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'表名'
--例如：
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'系统设置表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CM01_SYSTEM'

--为字段加注释：

exec sp_addextendedproperty
@name=N'MS_Description',
@value=N'说明',
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'表名',
@level2type=N'COLUMN',
@level2name=N'字段名'

--修改字段名称

语法：execute sp_rename '表名.字段名','新字段名'

exec sp_helpconstraint @objname='Evl_EvluationProsMarkGroup'

execute sp_rename 'Evl_EvluationProsMarkGroup.EvluationProsPlanId','EvluationProsId'

--删除字段约束

要想删除字段约束，第一步应查询当前字段所在表的所有约束

语法：select * from sysobjects where parent_obj in(select id from sysobjects where name='表名')

或者：exec sp_helpconstraint @objname='表名'

select * from sysobjects where parent_obj in(select id from sysobjects where name='Evl_EvluationProsPlan')

然后执行删除约束

语法：ALTER TABLE 表名 DROP CONSTRAINT FK__Evl_Evlua__Evlua__45472A22

ALTER TABLE Evl_EvluationProsMarkGroup DROP CONSTRAINT FK__Evl_Evlua__Evlua__45472A22

增加一个约束：

ALTER TABLE [dbo].[Evl_EvluationProsMarkGroup] WITH CHECK ADD CONSTRAINT [FK__Evl_Evlua__Evlua__52D62F6A] FOREIGN KEY([EvluationProsPlanId])
REFERENCES [dbo].[Evl_EvluationPros] ([EvluationProsId])

具体可以查看表的构造语句，譬如：

GO/****** Object: Table [dbo].[Evl_EvluationProsMarkGroup] Script Date: 2018/10/31 9:57:45 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[Evl_EvluationProsMarkGroup](
[MarkGroupId] [varchar]() NOT NULL,
[EvluationProsPlanId] [varchar]() NULL,
[GroupId] [varchar]() NULL,
[GroupName] [varchar]() NULL,
[RoleId] [varchar]() NULL,
[RoleName] [varchar]() NULL,
[UserId] [varchar]() NULL,
[UserName] [nvarchar]() NULL,
[CreateDate] [datetime] NULL,
CONSTRAINT [PK__Evl_Evlu__86E697BBFBDDA48D] PRIMARY KEY CLUSTERED
(
[MarkGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOSET ANSI_PADDING ON
GOALTER TABLE [dbo].[Evl_EvluationProsMarkGroup] ADD CONSTRAINT [DF__Evl_Evlua__Creat__53CA53A3] DEFAULT (getdate()) FOR [CreateDate]
GOALTER TABLE [dbo].[Evl_EvluationProsMarkGroup] WITH CHECK ADD CONSTRAINT [FK__Evl_Evlua__Evlua__52D62F6A] FOREIGN KEY([EvluationProsPlanId])
REFERENCES [dbo].[Evl_EvluationProsPlan] ([EvluationProsPlanId])
GOALTER TABLE [dbo].[Evl_EvluationProsMarkGroup] CHECK CONSTRAINT [FK__Evl_Evlua__Evlua__52D62F6A]
GO