Can we alter an existing synonym

No, SQL Server does not support ALTER SYNONYM. A synonym is a simple metadata alias stored in system catalogs that points to another object. Since it has no schema binding, no dependencies, and no executable definition, Microsoft designed it to be replaced rather than altered. To change a synonym target, we must DROP and recreate it.

A synonym is just a metadata pointer (name mapping) and stored in system catalog with no schema binding and no dependency tracking. It’s NOT like a view or proc with definition logic.

So, SQL Server designers kept it simple Change = replace pointer.

Let’s see the demo

Creating a table and inserting few records.

CREATE TABLE dbo.Employees (     EmpID INT PRIMARY KEY,     EmpName VARCHAR(50) );  insert into Employees(EmpID,EmpName) values (1,'Bagesh'),(2,'Rajesh'),(3,'Mahesh'),(4,'Amit')

 Now creating synonym

CREATE SYNONYM dbo.EmpSyn FOR dbo.Employees;

Let’s alter this.

ALTER SYNONYM dbo.EmpSyn FOR dbo.Employees;

Getting an error.

Correct way is to drop first and create it again

DROP SYNONYM dbo.EmpSyn; CREATE SYNONYM dbo.EmpSyn FOR dbo.Employees;

Run it