What happens with a synonym if the table which its based on gets deleted?

Question

we are working on a project to implement a large ERP system. We found out that one of the tables has a synonym to another database. Both databases are in the same instance. Our project leader has decided that one table has to be deleted. This table turns out to be the source table of the synonym.

In my opinion does this also effect the synonym. Am I right? I think, because the source table is deleted, the synonym cannot retrieve any data anymore and will have empty columns.

Answer 1

In this case the synonym should be properly deleted, if not, it cannot be used but still exists on sys.synonyms catalog.

You can check it running next script:

IF OBJECT_ID('T1') IS NOT NULL
    DROP TABLE T1;
GO

IF OBJECT_ID('NEW_T1') IS NOT NULL
    DROP SYNONYM NEW_T1;
GO

CREATE TABLE T1(f1 int);
GO

INSERT INTO T1 VALUES (1),(2),(3);
GO

CREATE SYNONYM NEW_T1 FOR T1;
GO

SELECT * FROM NEW_T1;
GO

f1
-----------
1
2
3

DROP TABLE T1;
GO

SELECT * FROM NEW_T1;
GO

Msg 5313, Level 16, State 1, Line 23
Synonym 'NEW_T1' references an invalid object.

SELECT name, object_id FROM sys.synonyms WHERE name = 'NEW_T1';
GO

name        object_id
------      --------------
NEW_T1      1463676262


IF OBJECT_ID('NEW_T1') IS NOT NULL
    DROP SYNONYM NEW_T1;
GO