Often DBAs get requests to alter the column types of a table. Although it needs to pass through multiple levels of approvals to implement structural changes in a production environment its quite common to get these requests. In this post I am focusing on how to alter a column type with a script.
CREATE TABLE dbo.Emp( EmpID INT IDENTITY (1,1) NOT NULL ,FirstName VARCHAR(50) NULL ,LastName VARCHAR(50) NULL ,HiredDate datetime NOT NULL ,Salary nvarchar(max))
Lets say I now got a request to change the type of salary column from nvarchar(max) to money. Here is the script to achieve this:
ALTER TABLE dbo.Emp1 ALTER COLUMN salary money NOT NULL
Here is how the table structure appears:
CREATE TABLE dbo.Emp1( [EmpID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [HiredDate] [datetime] NOT NULL, [Salary] [money] NOT NULL)
Hope this helps.