Renaming a column in MS SQL Server 2005

sql sql-server ddl renaming
Thomas Bratt picture Thomas Bratt · Aug 10, 2009 · Viewed 15.7k times · Source

What is the best practice when it comes to renaming a table column using SQL (MS SQL Server 2005 variant)? This assumes that there is data in the column that must be preserved.

Answer

Glen picture Glen · Aug 10, 2009

You have to use a stored proc to rename a column. The following will rename your column from 'oldColumnName' to 'newColumnName' without affecting any data.

EXEC sp_rename 'tableName.[oldColumnName]', 'newColumnName', 'COLUMN'

Obviously you'll have to update any code / stored procs / SQL that uses the old name manually.

Related questions

Alter Table Add Column Syntax

I'm trying to programmatically add an identity column to a table Employees. Not sure what I'm doing wrong with my syntax. ALTER TABLE Employees ADD COLUMN EmployeeID int NOT NULL IDENTITY (1, 1) ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY …

Read More
Is there a tool to generate a full database DDL for SQL Server? What about Postgres and MySQL?

Using Toad for Oracle, I can generate full DDL files describing all tables, views, source code (procedures, functions, packages), sequences, and grants of an Oracle schema. A great feature is that it separates each DDL declaration into different files (a …

Read More
Invalid column name on sql server update after column create

Does anyone see what's wrong with this code for SQL Server? IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'OPT_LOCK' AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK')) BEGIN ALTER TABLE REP_DSGN_SEC_GRP_LNK …

Read More