CODEWITHSUNDEEP
sql-server-2008
Mathematics
Mathematics

Reputation: 7628

Changing column name in a SQL Server table with 1000+ records

I have a column and want to change its name but nothing else using query, what would be the best way of doing it.

Upvotes: 1

Views: 7876

Answers (4)

il_guru
il_guru

Reputation: 8508

Calling the stored procedure sp_rename should be the easiest and fastest way

USE AdventureWorks2008R2;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460158

You can use the system stored-procedure sp_rename:

EXEC sp_rename 'dbo.Test.OldName', 'NewName', 'COLUMN';

Here's a complete sample with your 1000 records:

create table dbo.Test(ID int IDENTITY(1,1), OldName varchar(10));

declare @i int;
SET @i = 0;
WHILE @i < 1000
BEGIN
   SET @i = @i + 1;
   INSERT INTO dbo.Test Values(CAST(@i AS varchar(4))+ '. record');
END;

SELECT * FROM dbo.Test;

EXEC sp_rename 'dbo.Test.OldName', 'NewName', 'COLUMN';  

SELECT * FROM dbo.Test;

DROP TABLE dbo.Test;

Upvotes: 4

Kaf
Kaf

Reputation: 33829

You can use

sp_RENAME 'TableName.[Your_Old_ColumnName]' , '[Your_New_ColumnName]', 'COLUMN'

Note: renaming column name will break your queries, stored procedures etc..

Upvotes: 4

ORION
ORION

Reputation: 2411

 EXEC sp_rename 'myColumn', 'myColumn2', 'COLUMN'

Upvotes: 2

Related Questions