56

I tried the following code. Although I don't get any errors, it did not do it.

SELECT * FROM Categories EXEC sp_rename 'Active', CategoriesActive
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
zurna
  • 1,161
  • 4
  • 16
  • 20
  • And what are you trying to accomplish? To change the values in the table, or to change the name of a column in the table, or to change the names of multiple columns? – SWeko Mar 15 '10 at 15:15
  • Can you list what tables / columns you have and what you want to rename them to? – Robin Day Mar 15 '10 at 15:15

3 Answers3

89
EXEC sp_rename 'Categories.Active', 'CategoriesActive', 'COLUMN'
Li0liQ
  • 11,158
  • 35
  • 52
  • 3
    Also see [MSDN documentation for `sp_rename`](http://technet.microsoft.com/en-us/library/ms188351.aspx). – Uwe Keim Apr 02 '14 at 13:49
  • 1
    'Categories.Active' is the representation of 'table.old_column_name' and 'CategoriesActive' is the new column name. works fine. – Jimmy Salazar Apr 04 '23 at 18:14
51

FOR MSSQL :

EXEC sp_rename 'TABLENAME.OLD_COLUMNNAME', 'NEW_COLUMNAME', 'COLUMN';

FOR MYSQL : Use ALTER TABLE to do this

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name

You can rename a column using a CHANGE old_col_name new_col_name column_definition clause. To do so, specify the old and new column names and the definition that the column currently has. For example, to rename an INTEGER column from a to b, you can do this:

ALTER TABLE t1 CHANGE a b INTEGER;
Xander
  • 1,123
  • 9
  • 14
  • http://msdn.microsoft.com/en-us/library/ms190273.aspx does not seem to indicate a CHANGE clause to the ALTER TABLE statement – SWeko Mar 15 '10 at 15:23
  • Sorry I mis-read the question I thought he was referring to MYsql not MSsql! Will edit accordingly – Xander Mar 15 '10 at 15:26
  • actually it it wasn't for the "sp_rename" this very database-specific question would have been database agnostic :) – SWeko Mar 15 '10 at 15:28
6

You don't need to use that select in front, and the syntax should be like:

EXEC sp_rename 
    @objname = 'Categories.Active', 
    @newname = 'CategoriesActive', 
    @objtype = 'Type_of_your_column'
Marcos Placona
  • 21,468
  • 11
  • 68
  • 93