In SQL Server, you can use the sp_rename stored procedure to rename an object, including a column.

Example

Here’s an example to demonstrate:

EXEC sp_rename 't1.c1', 'c11';

This renames the column called c1 (in the t1 table) to c11.

Including the Object Type

You can also include the object type as a third argument. In this case, we can use COLUMN:

EXEC sp_rename 'dbo.t1.c11', 'c1', 'COLUMN';

Including the Schema Name

You can also qualify the first column with the schema name, in which case, it might look something like this:

EXEC sp_rename 'dbo.t1.c1', 'c11', 'COLUMN';

In this example, dbo is the schema name, but you will need to use whatever schema is applicable.

Including the Parameter Names

As with any stored procedure, you can also include the parameter names when calling sp_rename:

EXEC sp_rename 
    @objname = 'dbo.t1.c1',
    @newname = 'c11',
    @objtype = 'COLUMN';

Check for References

When you rename a column in SQL Server, you’ll probably see a message like this:

Caution: Changing any part of an object name could break scripts and stored procedures.

This is because when you rename a column, SQL Server does not automatically rename any references to that column. This is also true when you rename a table.

Despite the above cautionary message, the column is renamed anyway.

If we try to query a view that referenced the renamed column, we get an error.

SELECT * FROM vt1;

Result:

Msg 207, Level 16, State 1, Procedure vt1, Line 2
Invalid column name 'c1'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'vt1' because of binding errors.

This view is trying to reference c1 but that column has been renamed, and so there are no columns with that name in the table.

This view’s definition looks like this:

CREATE VIEW vt1 AS
SELECT c1, c2 FROM dbo.t1;

We would need to update this view to reference the new column name. So we could do this:

ALTER VIEW vt1 AS
SELECT c11, c2 FROM dbo.t1;

Now querying the view will return the correct data.

Therefore, before you rename any columns, you should always check for scripts and stored procedures that reference that column. You will need to update such scripts and procedures to reference the new column name.

You can use the sys.sql_expression_dependencies system catalog view to do this check.

Renaming Computed Columns

SQL Server doesn’t let us rename computed columns.

If you try to rename a computed column, you’ll probably see the following error message.

EXEC sp_rename 'dbo.t1.c3', 'c13', 'COLUMN';

Result:

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 4928, Level 16, State 1, Procedure sp_rename, Line 689
Cannot alter column 'c3' because it is 'COMPUTED'.

In this case, the c3 column is a computed column that does a calculation based on a value in the c2 column.

Here’s what happens when we try to rename the c2 column.

EXEC sp_rename 'dbo.t1.c2', 'c12', 'COLUMN';

Result:

Msg 15336, Level 16, State 1, Procedure sp_rename, Line 563
Object 'dbo.t1.c2' cannot be renamed because the object participates in enforced dependencies.

We get a different error for this column. This error is because the computed column depends on this column.

Basically, to rename the computed column, we would need to drop it and add it again.

Example:

ALTER TABLE t1
DROP COLUMN c3;

ALTER TABLE t1
ADD c13 AS c2 * 10;

Foreign Keys

Renaming a primary key column doesn’t break any foreign keys that reference that column.

For example, before I renamed t1.c1 to t1.c11, although I didn’t mention it, I actually had another table and column (t2.c2) that referenced t1.c1.

After renaming t1.c1 to t1.c11, the foreign key constraint on t2.c2 was still enforcing any new inserts to comply with the foreign key, even though the primary key column in the other table had had its name changed.