Rename Column

To rename a column in Microsoft SQL Server, you need to use the sp_rename system stored procedure because SQL Server does not have a direct RENAME COLUMN statement.

---

Syntax:

EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

---

Steps:

1. Specify the Table and Old Column Name:
   • Use the format table_name.old_column_name.
2. Specify the New Column Name:
   • Provide the desired column name.
3. Specify the Object Type:
   • Always use 'COLUMN' as the object type to indicate a column rename.

---

Example:

Rename the column emp_name to employee_name in the employees table:

EXEC sp_rename 'employees.emp_name', 'employee_name', 'COLUMN';

---

Notes:

1. Renaming a column does not automatically update references to it (e.g., in views, stored procedures, triggers, or queries). These must be updated manually.
2. Be cautious when renaming columns in a production environment to avoid breaking dependencies.

---

Alternative (Indirect Method):

If you prefer not to use sp_rename, you can:

1. Add a new column with the desired name.
2. Copy data from the old column to the new column.
3. Drop the old column.

Example:

-- Step 1: Add a new column
ALTER TABLE employees ADD employee_name NVARCHAR(100);
 
-- Step 2: Copy data to the new column
UPDATE employees SET employee_name = emp_name;
 
-- Step 3: Drop the old column
ALTER TABLE employees DROP COLUMN emp_name;

Conclusion: Using sp_rename is the simplest and most efficient way to rename a column.