How Do I Rename A Column In A Database Table Using Sql?

If I wish to simply rename a column (not change its type or constraints, just its name) in an SQL database using SQL, how do I do that? Or is it not possible? This is for any data

Solution 1:

Specifically for SQL Server, use sp_rename

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

Solution 2:

On PostgreSQL (and many other RDBMS), you can do it with regular ALTER TABLE statement:

=>SELECT*FROM Test1;
 id | foo | bar 
----+-----+-----2|1|2=>ALTERTABLE Test1 RENAME COLUMN foo TO baz;
ALTERTABLE=>SELECT*FROM Test1;
 id | baz | bar 
----+-----+-----2|1|2

Solution 3:

In MySQL, the syntax is ALTER TABLE ... CHANGE:

ALTER TABLE <table_name> CHANGE <column_name><new_column_name><data_type> ...

Note that you can't just rename and leave the type and constraints as is; you must retype the data type and constraints after the new name of the column.

Solution 4:

Unfortunately, for a database independent solution, you will need to know everything about the column. If it is used in other tables as a foreign key, they will need to be modified as well.

ALTERTABLE MyTable ADD MyNewColumn OLD_COLUMN_TYPE;
UPDATE MyTable SET MyNewColumn = MyOldColumn;
-- add all necessary triggers and constraints to the new column...-- update all foreign key usages to point to the new column...ALTERTABLE MyTable DROPCOLUMN MyOldColumn;

For the very simplest of cases (no constraints, triggers, indexes or keys), it will take the above 3 lines. For anything more complicated it can get very messy as you fill in the missing parts.

However, as mentioned above, there are simpler database specific methods if you know which database you need to modify ahead of time.

Solution 5:

I think this is the easiest way to change column name.

SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'