Article By:

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:

CREATE TABLE dbo.TEMPTEST (ID INT)
INSERT  INTO dbo.TEMPTEST
    ( ID )
VALUES
    ( 0 ),
    ( 1 ),
    ( 2 );
CREATE SYNONYM dbo.syn_TEMPTEST FOR dbo.TEMPTEST;
SELECT * FROM dbo.syn_TEMPTEST
TRUNCATE TABLE dbo.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.
The following procedure will TRUNCATE a table from a SYNONYM name.

CREATE PROCEDURE dbo.spr_TruncateTableBySynonymName (
      @SchemaName AS sysname
    , @SynonymName AS sysname
    )
AS
BEGIN --Procedure
    SET NOCOUNT ON;
    DECLARE @BaseObjectName AS NVARCHAR(1035) = NULL;
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @ErrorMessage NVARCHAR(255);
    SELECT TOP 1
        @BaseObjectName = base_object_name
    FROM
        sys.synonyms
        INNER JOIN sys.schemas ON schemas.schema_id = synonyms.schema_id
    WHERE
        schemas.name = @SchemaName
        AND synonyms.name = @SynonymName
        AND type = 'SN'
        AND is_ms_shipped = 0;
    IF @BaseObjectName IS NOT NULL
        BEGIN
            SET @SQL = N'TRUNCATE TABLE ' + @BaseObjectName;
            BEGIN TRY
                EXECUTE sys.sp_executesql @SQL;
                IF OBJECT_ID('tempdb..#RowCount', 'U') IS NOT NULL
                    DROP TABLE #RowCount;
                CREATE TABLE #RowCount ( ReturnValue INT );
                SET @SQL = N'INSERT INTO #RowCount SELECT Count(*) FROM ' + @SchemaName + '.' + @SynonymName;
                EXECUTE sys.sp_executesql @SQL;
                DECLARE @RowCount INT = 0;
                SELECT TOP 1
                    @RowCount = ReturnValue
                FROM
                    #RowCount;
                IF @RowCount > 0
                    BEGIN
                        SET @ErrorMessage = 'The base table for synonym ' + @SchemaName + '.' + @SynonymName + ' was not truncated.';
                        RAISERROR(@ErrorMessage,16,1);
                    END;
            END TRY
            BEGIN CATCH
                THROW;
            END CATCH;
        END;
    ELSE
        BEGIN
            SET @ErrorMessage = 'The base table for synonym ' + @SchemaName + '.' + @SynonymName + ' was not found.';
            THROW 50000,@ErrorMessage,1;
        END;
END;

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:

IF OBJECT_ID('dbo.syn_TEMPTEST', 'SN') IS NOT NULL
    DROP SYNONYM dbo.syn_TEMPTEST
IF OBJECT_ID('dbo.TEMPTEST', 'U') IS NOT NULL
    DROP TABLE dbo.TEMPTEST
CREATE TABLE dbo.TEMPTEST (ID INT)
INSERT  INTO dbo.TEMPTEST
    ( ID )
VALUES
    ( 0 ),
    ( 1 ),
    ( 2 );
CREATE SYNONYM dbo.syn_TEMPTEST FOR dbo.TEMPTEST;
SELECT * FROM dbo.syn_TEMPTEST --Returns three rows.
--TRUNCATE SYNONYM:
EXECUTE dbo.spr_TruncateTableBySynonymName @SchemaName = 'dbo', @SynonymName = 'syn_TEMPTEST'
SELECT * FROM dbo.syn_TEMPTEST --Returns zero rows.
IF OBJECT_ID('dbo.syn_TEMPTEST', 'SN') IS NOT NULL
    DROP SYNONYM dbo.syn_TEMPTEST
IF OBJECT_ID('dbo.TEMPTEST', 'U') IS NOT NULL
    DROP TABLE dbo.TEMPTEST

 

Need Assistance?

Speak with an Engineer

Not sure where to start? We're here to help walk you through the process, understand your environment, and provide the guidance you need to achieve cybersecurity maturity. Get in touch today.

Get in Touch