修改SQL Server字段类型

首次发布:2021-11-25 14:45

sql server可视化工具(SSMS)不能直接修改数据库字段类型,但可以通过sql 语句修改字段类型,修改语法如下

Alter table [表名] Alter column [列名] [列类型]
--原创来源http://www.luofenming.com/show.aspx?id=ART2021112500001

应用实例

假如我们有一个这样的数据表

CREATE TABLE [dbo].[UserInfo](
	[Id] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
	[userName] [nvarchar](50) NOT NULL,
	[nickName] [nvarchar](50) NULL,
	[userPwd] [nchar](32) NOT NULL,
	[userKey] [nchar](32) NOT NULL,
	[userPower] [varchar](200) NOT NULL,
	[CreateTime] [datetime] NULL,
 CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

我们现在想把CreateTime修改成不可为空,修改语句如下

Alter table [dbo].[UserInfo] Alter column [CreateTime] [datetime]  NOT NULL