Microsoft Gold Cloud CRM and Gold Cloud Platform Partner

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:

CREATE SYNONYM dbo.syn_TableName FOR DevServerName2.DatabaseName2.dbo.TableName;

Then in TestSeverName1, you would redefine the same synonym name to point to the appropriate object in that environment:

CREATE SYNONYM dbo.syn_TableName FOR TestServerName2.DatabaseName2.dbo.TableName;

And the same pattern would apply in Production:

CREATE SYNONYM dbo.syn_TableName FOR ProductionServerName2.DatabaseName2.dbo.TableName;

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:

RESULT:

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

The following procedure will TRUNCATE a table from a SYNONYM name.

After defining that stored procedure, you can TRUNCATE a table via the synonym name using this code:

EXECUTE dbo.spr_TruncateTableBySynonymName @SchemaName = ‘dbo’, @SynonymName = ‘syn_TEMPTEST’

Here’s a simple test case you can use to show that the procedure works as expected: