Posts Tagged ‘Rename Column’

SQL Basics – Rename or Change DataType of a Column in SQL Server

July 1, 2009 1 comment

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]

-- 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')

SELECT * FROM [dbo].[Employee]

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

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