JZTXT

记录常用SQL

发布时间 2023-04-12 16:26:08作者: o李一波o
创建表
CREATE TABLE WorkOrderTimeFlowRecord(
	Id int identity(1,1) primary key,
	
	CreatedTime datetime NULL,
	CreatedUserId varchar(50) NULL,
	
	UpdatedProgressTime datetime NULL,
	UpdatedProgressUserId varchar(50) NULL,
	
	TurnAssignTime datetime NULL,
	TurnAssignUserId varchar(50) NULL,
	AssignUserIds varchar(50) NULL,
	
	ReplyTime datetime NULL,
	ReplyUserId varchar(50) NULL,
	
	ClosuredTime datetime NULL,
	ClosuredUserId varchar(50) NULL,
	
	CanceledTime datetime NULL,
	CanceledUserId varchar(50) NULL,
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工单时间流水记录' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkOrderTimeFlowRecord'
添加字段注释，注意：如果当前查询处于当前库，@level0name不能写库名，写dbo即可，否则报错：
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkOrderTimeFlowRecord', @level2type=N'COLUMN',@level2name=N'CreatedTime'
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkOrderTimeFlowRecord', @level2type=N'COLUMN',@level2name=N'CreatedUserId'
修改字段名称
--更改ClosureTime字段名称
-- ALTER TABLE SinadealTradeDB.dbo.WorkOrder rename column ClosureTime to ClosuredTime
EXEC sp_rename 'WorkOrder.[ClosureTime]','ClosuredTime','COLUMN';
删除字段
ALTER TABLE SinadealTradeDB.dbo.WorkOrder drop column LastReplyTime