How to TRUNCATE a SQL Server Table from a SYNONYM Name

If you work in an environment where your SQL Server database procedures need to frequently reference data in other databases or on other servers, it can be difficult to implement development, test, and production environments while still maintaining a single code-base because you find you have to change your 4-part names in each Dev, Test and Production environment. One solution that can seriously help manage that situation is by employing SQL Server Synonyms within your code to point to the user-defined tables in the other environments/databases using the 4-part name of those tables, such as: ServerName.DatabaseName.SchemaName.TableName.

In the server named DevServerName1, you would reference DevServerName2 objects with synonyms such as:

The beauty of this technique is that you maintain the synonym definitions as a single script all in one place in each environment and use the constant synonym names in your stored procedures, views, and other objects so that you’re not having to redefine each 4-part name in each SQL object in each environment.

While you can reference synonyms that point to tables with INSERT, UPDATE, DELETE, UPSERT, and JOIN, you’ll find one limitation of synonyms is that you cannot TRUNCATE tables by their synonym name out of the box. This is partially because synonyms can reference more than just user-defined tables and the TRUNCATE TABLE syntax expects a table name, not a synonym name.

Truncating a table is much faster than deleting all the rows from the tables because of the logging involved in each

If you attempt to truncate a table via a valid synonym name for that table, you’ll see the following error where syn_MyTablePointer is your synonym name:

Msg 4708, Level 16, State 2, Line 21
Could not truncate object ‘dbo.syn_MyTablePointer’ because it is not a table.

EXAMPLE:

Example of how TRUNCATE TABLE doesn't work with SYNONYMS

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATETABLEdbo.TEMPTEST (IDINT)

INSERTINTOdbo.TEMPTEST

(ID)

VALUES

(0),

(1),

(2);

CREATESYNONYMdbo.syn_TEMPTESTFORdbo.TEMPTEST;

SELECT*FROMdbo.syn_TEMPTEST

TRUNCATETABLEdbo.syn_TEMPTEST--Results in an error

RESULT:

Msg 4708, Level 16, State 2, Line 21
Could not truncate object ‘dbo.syn_TEMPTEST’ because it is not a table.