SQL with Manoj

In my [previous post] we saw how to create a new Database and a new Table. We also saw how to [ADD/DROP] columns in a table.
 

Today we will see how to Rename a Column and Change its datatype.
 

–> To Rename an existing column in a table you can use the system Stored Procedure sp_rename, and the syntax is:

EXECUTE sp_rename 'schema.Table.ColumnName', 'NewColumnName', 'COLUMN'
 

–> And to change column datatype use simple ALTER TABLE statement, like:

ALTER TABLE table_name
ALTER COLUMN ColumnName new_Datatype
 

Check video on how to Rename and Change column datatype in a Table:

–> SQL Script used in the above demo:

USE [Demo]
GO

-- Create a sample table:
CREATE TABLE [dbo].[Employee](
	[EmployeeID]	int				NOT NULL,
	[EmployeeName]	nvarchar(100)	NOT NULL,
	[Gender]		nchar(1)		NULL,
	[DOB]			datetime		NULL,
	[DOJ]			datetime		NULL,
	[DeptID]		int				NULL
)

INSERT INTO [dbo].[Employee]
VALUES (1, 'MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee]
VALUES (2, 'JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeID], [EmployeeName])
VALUES (3, 'Brock H')
GO

SELECT * FROM [dbo].[Employee]
GO

-- Rename a column:
EXECUTE sp_rename N'dbo.Employee.MgrID', N'ManagerID', 'COLUMN'
GO

-- Change datatype of column:
ALTER TABLE [dbo].[Employee]
ALTER column ManagerID BIGINT
GO